# Total Query

R

#### Rita

Totals Query

I created a payroll database that tracks piecemeal work for clients with
disabilities. The subreport has fields for calculating their pay. I need to
create a report for Clients that will get a paycheck for \$25 and over.

The subreport:
I'm doing quite a bit of calculating to get the pay for the job.
Gross Pay: IIf([Gross Pay Formula]>0,[Gross Pay Formula],[q
sf/Code]![Subsidy])
Gross Pay Formula: IIf([Prevailing Wage]>0,[Piece Rate Wage],[Hourly Wage])
Hourly Wage: IIf(IsNull([q sf/Code]![Prevailing Wage]),([q sf/Code]![Avg
Pay])*[q sf/Code]![Time])
Avg Piece Rate: [q sf/Code]![Piece Rate Wage]/[q sf/Code]![Time_wo_Hourly]
Avg Piece Rate Wage: IIf([Time_wo_Hourly]>0,[Piece Rate
Wage]/[Time_wo_Hourly])

My main report contains a Timesheet_ID that it used with the subreport to
pull in all the individual jobs worked in the pay period. The user is
prompted to enter â€œPay Period Endingâ€. This week they enter â€œ9/5â€ and that
pulls

What I need now is to display a report showing the Clients getting over \$25,
and Iâ€™m not sure the easiest/most efficient way of going about it. I don't
know how to extract this from the subreport.

I had a suggestion to use the Totals Query! Iâ€™ve never used it, but I read
the article, http://support.microsoft.com/kb/290136, and created the running
total in the Northwind Database.
They used =DSum(â€œ[Field you want to total]â€,â€Tableâ€,â€Criteria). This would
be great to use this field to check >\$25!

Is a Totals Query possible with these parameters, or can it just be used on
fields with numbers and tables?

=DSum(â€œ[Gross Pay]â€,â€What do I enter hereâ€,â€What would be entered here?â€).

THANKS!

Here is an example
____________________________________________________________________
ClientID Header: (This information pulls from a table with just client names
and pay period)
[ClientID]:315 [Name]:Tom Jones [Pay Period ending]: 9/5/08
[TimesheetID]:1511
------------------------------------------------------------------------------------------------
Detail:
My subreport is in the detail section pulling from a separate table with
just this information:
Job Piece Gross Timesheet
Date Code Units Rate Time Pay ID
8/25/2008 74374 184 \$0.00 2.00 \$15.34 1511
8/26/2008 74361 50 \$0.00 0.50 \$2.47 1511
8/26/2008 74374 108 \$0.00 1.25 \$9.00 1511
8/28/2008 74374 173 \$0.00 2.25 \$14.42 1511
8/29/2008 74361 117 \$0.00 1.25 \$5.77 1511

i presume the subreport is running from a query saved to the databse
somewhere

try

select clientid,name,[pay period ending],timesheetid, sum(grosspay) as
sumofgrosspay
from thequeryforthesubreport
group by clientid,name,[pay period ending],timesheetid
having sum(grosspay) > 25

as a query working off the query for the above subreport

hope this helps

Regards
Kelvan

Oh I can't believe it was that easy!!!!!!!!!!!!!!!! I was trying to do crazy
things to get this to work!!!! Thanks you soooo much!

yeah no problem glad to help