Adding regular and overtime hours and multiplying by rate

[PureEvil]

I have a database tracking employee labour. I'm trying to print a report
that shows dollar amounts as well as the hours for a particular department.
Data entered:
ST - standard time
OT - time and a half
DT - double time
wage
employee
department
task
project

I need to calculate (ST+OT*1.5+DT*2)*wage. I've added the wage to the ST OT
DT line and for the first part of the report it calculates ST*wage just fine,
but if the rate changes, it continues to calculate the original rate even
though the correct wage displays on the report - it's the math that is wrong.
Once I get that working I need to subtotal for each employee and then a
final total amount.

Date Depart ST OT DT Wage Total
XX John Doe
June 1 R&D 8 3 4 14.30 293.15
((8+(3*1.5)+(4*2))*14.30)

I can do this easily in Excel but the how-to alludes me in Access. Can
anyone help please? I'm using Access 2003, if you need more information let
me know.

Pamala

 

[Allen Browne]

Instead of 3 fields (ST, OT, and DT), you need 3 records for a person who
has all 3 categories.

Presumably you have a Staff table (one record for each person, with a
StaffID primary key), and a Pay table (where you record payments to staff.)
At the very simplest level, the Pay table might have fields like this:
PayID AutoNumber primary key
StaffID Number relates to tblStaff.StaffID (who this
row is for)
PayDate Date/Time when this person got this payment
Hours Number number of hours paid for.
Factor Number multiplier for ST (1), OT (1.5), or DT
(2)
HrRate Currency How much per hour.

Now, taking your example where StaffID 99 gets paid for 8 hrs ST + 3 hrs OT
+ 4 hrs DT at $14.30/hr on June 1, this table would have these 3 records:
StaffID PayDate Hours Factor HrRate
99 Jun 1 08 8.0 1.0 14.30
99 Jun 1 08 3.0 1.5 14.30
99 Jun 1 08 4.0 1.0 14.30

You can now create a query, and type in:
Amount: [Hours] * [Factor] * [HrRate]
to get the amount for each row.

The crucial concept in a relational database is the idea that you use a
one-to-many relation to model this data, i.e there are many records in a
related table instead of many columns (spreasheet style.)

There's lots more to sort out, but that's the core idea. For example, if you
want to show just the total per staff member per pay date, depress the Total
button on the toolbar. This adds a Total row to the query design grid. Under
StaffID and PayDate, accept Group By. Under the Amount field, choose Sum.