benallen002
Board Regular
- Joined
- Feb 16, 2005
- Messages
- 65
Hi all. Being such a big fan of this board, I thought I would come to you guys for help on designing a report and maybe implementing it as well. Here is what I am trying to do:
I have an employee time tracking database setup that tracks each employee's time at an atomic level. Each employee has 3 different sets of hours recorded, ST (straight time) OT (overtime) and DT (doube-time), and these hours are assigned a date. These hours get distributed over any number of job tasks and the tasks get rolled up into job numbers, which in turn get rolled up into project numbers. Now, the database works great and all my tables and querys are structured well (I think, i'm still new to Access) and I can do the report based on a DAILY format; the problem comes when i want to print a WEEKLY time report that includes all the ST, OT, and DT hours per employee line at the Detail level. What I need to have for each line (employee) in the Detail section are the hours from each of the seven days of the week that the report is for, and have them properly split among the three sets of hours for each employee for each day.
Basically, the way it looks like now is each employee has 3 rows (ST, OT, DT) and 7 columns (days of the week). I currently do this in Excel, and while it is easy to do this using VLOOKUPs and other formulas, overall Excel is horrible at what I'm trying to accompllsh.
So, any help/suggestions you guys might have on how to structure this report and its underlying queries would be much appreciated. Right now it is looking like I will have run a sub-report for each day of the week...but then how can I feed it the date of each day based on the weekending date the report is for?
I have an employee time tracking database setup that tracks each employee's time at an atomic level. Each employee has 3 different sets of hours recorded, ST (straight time) OT (overtime) and DT (doube-time), and these hours are assigned a date. These hours get distributed over any number of job tasks and the tasks get rolled up into job numbers, which in turn get rolled up into project numbers. Now, the database works great and all my tables and querys are structured well (I think, i'm still new to Access) and I can do the report based on a DAILY format; the problem comes when i want to print a WEEKLY time report that includes all the ST, OT, and DT hours per employee line at the Detail level. What I need to have for each line (employee) in the Detail section are the hours from each of the seven days of the week that the report is for, and have them properly split among the three sets of hours for each employee for each day.
Basically, the way it looks like now is each employee has 3 rows (ST, OT, DT) and 7 columns (days of the week). I currently do this in Excel, and while it is easy to do this using VLOOKUPs and other formulas, overall Excel is horrible at what I'm trying to accompllsh.
So, any help/suggestions you guys might have on how to structure this report and its underlying queries would be much appreciated. Right now it is looking like I will have run a sub-report for each day of the week...but then how can I feed it the date of each day based on the weekending date the report is for?