Simple Math for Payroll Report 2

[SMHSleepy]

Hello all,

I have a report based on a query which filters for a specific employee and date range and displays all the shifts worked. The report looks something like this:

Name WorkDate ShiftCode Hours

John 2011-08-22 D 8
John 2011-08-23 E 12
John 2011-08-24 LOA 4

In this example, the paid hours are D+E = 8+12 = 20.
LOA = Leave Of Absence = 4 unpaid hours.

I'd like to have a "TOTALS" section on the bottom of the report which breaks down paid and unpaid hours. For example:

Total paid hours: 20
Total unpaid hours: 4

My question is, where and how do I code the math for this? Can I do it at the report level or query? Do I create an invisible form, then extract the numbers to the report?

 

[SkipVought]

hi,

I'd create a table that has your business rules about paid and unpaid for each shift code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SMHSleepy]

Could you please elaborate? Do I use a make-table query? where exactly do the business rules go? Thanks.

 

[SkipVought]

[tt]
ShiftCodeRules

ShiftCode PayEmp
D TRUE
E TRUE
LOA FALSE
[/tt]
Just create using the available buttons (no vba)

Then in a query, join with your other table on shiftcode and use the PayEmp to determine the Pay.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

I expect what Skip is suggesting is creating a table like:
[tt][blue]
tblShiftCodes
================================
ShiftCode text primary key
ShiftTitle text descriptions
ShiftPaid Num 1 for paid and 0 for not paid[/blue][/tt]

You can then join this table into your query and multiply the ShiftPaid times Hours to get paid hours.

Duane
Hook'D on Access
MS Access MVP

 

[SMHSleepy]

Okay, I get it but how do I get the TOTAL hours?

 

[dhookom]

In the report footer section, you would have a text box with a control source like:

=Sum([Hours Field/Expression] * [ShiftPaid])

Duane
Hook'D on Access
MS Access MVP

 

[SMHSleepy]

Awesome. Of course, my actual database is more complicated than the example so I'll work on it and report back. Thanks guys.

 

[SMHSleepy]

Works like a charm. Made a table with all the possible scenarios: i.e. Regular, LOA, weekend premium, vacation, sick. I put a 1 where appropriate and a 0 where not. Then I joined the table in my query and used the SUM control source in my report just like you described above. Result is something like this:

TOTAL HOURS

Regular: 60
Sick: 8
Weekend Premium: 12
Vacation: 8
LOA (unpaid): 4

Thanks again!