MS Access Help...?

Right, I'm making this database to help with a job costing system (recording employees times week by week using dates) using 3 different pay rates which are individual to each employee.

I'm getting on fine with it, but i've hit a problem! I need an effective way of entering in the hours worked on each pay rate.

It needs to be done with the dates run along the top as column headers, then each employee down as a row header, showing their payrate as well (a max of 2 for each week!

It's all rather complicated and tbh, i've started to rip my hair out over it, does anyone have any good ideas?

thanks

Rich

 

Also, is it possible to use a cross-tab for data entry?

 

If I understand correctly, you want to list each employee as a row against a week and show their pay against their respective individual payrate?

I don't use MS Access, but I do use MySQL for web development so I did a quick test.

DB tables:

Code:

pay_rates
+----+----------+------------+---------+------------+
| id |  emp_id  |  group_id  |  hours  |    date    |
+----+----------+------------+---------+------------+
| 01 | 0566     | 01         | 30      | 2008-09-22 |
| 02 | 0566     | 02         | 5       | 2008-09-22 |
| 03 | 0566     | 03         | 2.5     | 2008-09-22 |
| 04 | 0567     | 01         | 35.5    | 2008-09-22 |
| 05 | 0567     | 01         | 2       | 2008-09-22 |
| 06 | 0566     | 03         | 37.5    | 2008-09-22 |
+----+----------+------------+---------+------------+

pay_groups
+----+--------+---------+
| id |  group | pay     |
+----+--------+---------+
| 01 | A      | 6.50    |
| 02 | B      | 8.50    |
| 03 | C      | 9.75    |
+----+--------+---------+

Then your sql would be like:

Code:

SELECT pr.emp_id, SUM(pg.pay * pr.hours) AS earned, pr.date
FROM pay_groups pg, pay_rates pr
WHERE pr.group_id = pg.id
GROUP BY pr.emp_id, pr.date

Result:

Code:

Array
(
    [emp_id] => 566
    [earned] => 365.625
    [date] => 2008-09-21
)

Array
(
    [emp_id] => 566
    [earned] => 261.875
    [date] => 2008-09-22
)

Array
(
    [emp_id] => 567
    [earned] => 247.75
    [date] => 2008-09-22
)

 

doh!