SUM failing on group footer

[p-rat]

I have a report that comes from a query. This report shows detailed
rows of TruckTickets. It is grouped by Driver, Location, and Date. I
have a field in this query that comes from a payroll table that shows
Driver paid hours by the date. In the query this DriverPaidHours field
shows up on each TruckTicket or each detail row, but I hide this and
then use MAX(DriverPaidHours) in the Driver group footer. ShopHours
column is then MAX(DriverPaidHours) - TruckHours. It should look like
this:

DALLAS,TX
driver truckticket customer driverpaidhours truck hours
shophours

sam 1 cust1 12.0 (hidden)
4.0
sam 2 cust2 12.0 (hidden) 4.0
sam 3 cust3 12.0 (hidden) 4.0
___________________________________________________________
sam 12.0
12.0 0.0

tom 4 cust4 11.0 (hidden)
9.0
___________________________________________________________
tom 11.0
9.0 2.0


DALLAS total 23.0
21.0 2.0


I can't get this MAX(DriverPaidHours) to SUM correctly on the Location
group footer or any other group footer. I've tried the runningsum
property of overgroup and overall and this doesn't work. If anyone
could help me out I'd appreciate it GREATLY! Thanks.

 

[Dale Fye]

Can you post the SQL string for the report?

I don't think I would even include the Driver Paid Hours column in the
details section of the report. I think I would put a DLOOKUP call in the
control source of the textbox in the Driver footer. Something like:

=dlookup("DriverPaidHours", "SometableOrQuery", "DriverID = " & me.DriverID)

Then, in the Location footer textbox, I'd do another DSum that would sum
accross all the drivers for that location.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[p-rat]

I would post it but it would probably end up with more confusion. I
have allot of calculations, etc. that aren't in this post. I like your
idea, but I've never used a DLookup before. This is all pretty new to
me. However I need the criteria for the dlookup to include more than
just the DriverID as you have below. Can you add the Date as part of
the criteria AND the DriverID?

 

[Dale Fye]

sure.

I just didn't see any date fields or anything like that. You can use any
field that is available in your report, so your criteria string might look
like:

=dlookup("DriverPaidHours", "SomeTableOrQuery", "DriverID = " & me.DriverID
& " AND [DateField] = #" & me.DateField & "#")

Of course you would have to a have a date field in both the report, and in
the table you are using the DLOOKUP ( ) to point to.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[p-rat]

OK. Which part of the criteria is the report side and which is the
query side? is the 'me.' the report side? I'm getting a NAME? error
now. Sorry about my lack of knowledge on this, but I'm trying to
struggle my way through this. Thanks.

sure.

I just didn't see any date fields or anything like that.  You can use any
field that is available in your report, so your criteria string might look
like:

=dlookup("DriverPaidHours", "SomeTableOrQuery", "DriverID = " & me.DriverID
& " AND [DateField] = #" & me.DateField & "#")

Of course you would have to a have a date field in both the report, and in
the table you are using the DLOOKUP ( ) to point to.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

I would post it but it would probably end up with more confusion. I
have allot of calculations, etc. that aren't in this post. I like your
idea, but I've never used a DLookup before. This is all pretty new to
me. However I need the criteria for the dlookup to include more than
just the DriverID as you have below. Can you add the Date as part of
the criteria AND the DriverID?

- Show quoted text -

 

[Dale Fye]

Yes,

The me.[fieldname] is the report side of things.

So the [DriverID] and [DateField] should refer to the date field in the
table that is being queried by the DLOOKUP. The me.DriverID and me.DateField
refer to the field names that are in the query that is the source for the
report.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

OK. Which part of the criteria is the report side and which is the
query side? is the 'me.' the report side? I'm getting a NAME? error
now. Sorry about my lack of knowledge on this, but I'm trying to
struggle my way through this. Thanks.

sure.

I just didn't see any date fields or anything like that. You can use any
field that is available in your report, so your criteria string might look
like:

=dlookup("DriverPaidHours", "SomeTableOrQuery", "DriverID = " & me.DriverID
& " AND [DateField] = #" & me.DateField & "#")

Of course you would have to a have a date field in both the report, and in
the table you are using the DLOOKUP ( ) to point to.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

I would post it but it would probably end up with more confusion. I
have allot of calculations, etc. that aren't in this post. I like your
idea, but I've never used a DLookup before. This is all pretty new to
me. However I need the criteria for the dlookup to include more than
just the DriverID as you have below. Can you add the Date as part of
the criteria AND the DriverID?

Can you post the SQL string for the report?

I don't think I would even include the Driver Paid Hours column in the
details section of the report. I think I would put a DLOOKUP call in the
control source of the textbox in the Driver footer. Something like:

=dlookup("DriverPaidHours", "SometableOrQuery", "DriverID = " & me.DriverID)

Then, in the Location footer textbox, I'd do another DSum that would sum
accross all the drivers for that location.
email address is invalid
Please reply to newsgroup only.

:
I have a report that comes from a query. This report shows detailed
rows of TruckTickets. It is grouped by Driver, Location, and Date. I
have a field in this query that comes from a payroll table that shows
Driver paid hours by the date. In the query this DriverPaidHours field
shows up on each TruckTicket or each detail row, but I hide this and
then use MAX(DriverPaidHours) in the Driver group footer. ShopHours
column is then MAX(DriverPaidHours) - TruckHours. It should look like
this:

DALLAS,TX
driver truckticket customer driverpaidhours truck hours
shophours

sam 1 cust1 12.0 (hidden)
4.0
sam 2 cust2 12.0 (hidden) 4.0
sam 3 cust3 12.0 (hidden) 4.0
___________________________________________________________
sam 12.0
12.0 0.0

tom 4 cust4 11.0 (hidden)
9.0
___________________________________________________________
tom 11.0
9.0 2.0

DALLAS total 23.0
21.0 2.0

I can't get this MAX(DriverPaidHours) to SUM correctly on the Location
group footer or any other group footer. I've tried the runningsum
property of overgroup and overall and this doesn't work. If anyone
could help me out I'd appreciate it GREATLY! Thanks.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -