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
 
L

Lord Kelvan

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
 
R

Rita

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top