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 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