Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
osupratt
Posting Yak Master
238 Posts |
 Posted - 2009-01-08 : 12:30:50
|
I have a query that pulls info from two tables. The first table is TruckTicket information (date,location,driver,truckhours). The second table is a payroll table that I get the field 'driverpaidhours'. The driver for a certain date will probably have multiple trucktickets for one location; however, the payroll portion or field 'driverpaidhours' is the total hours he was paid for the same date. The query is used in a report. The report should look like below.1/6/2009DALLAS,TXdriver truckticket truckhours driverpaidhours shoptimetom 123 6 12 tom 124 6 12 _____________________________________________________________tom 12.0 12.0 0.0sam 125 4 10 sam 126 2 10_____________________________________________________________sam 6.0 10.0 4.0__________________________________________________________________________________________________________________________DALLAS,TX 18.0 22.0 4.0 SHOPTIME field is 'driverpaidhours' - 'truckhours' in the group footers. I need to use MAX(driverpaidhours) in the driver group footer which is fine. I can't however SUM this properly in the next footer - LOCATION or any after this. If I use SUM it's calculating every detail row which I don't want it to. So instead of 22.0 hours showing above in the location group footer it is showing me 44 hours.I've tried the property runningsum with over group and over all and this does not work. I hope someone can give me some guidance on what I need to do. I would appreciate any help you might be able to offer. Thanks.This is on an access 2003.(added code tags to the report to make it more readable - Jeff) |
|
|
|
|
|
SUM not working on group footer