Report Design Questions (calling all cars!)

[benallen002]

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?

 

[benallen002]

Vic,

****, I was really hoping for some money. Yes, the columns on my report are the days of the week. I used the code that Denis gave me back on page one of this post to convert the actual dates in the field names to days of the week numbers. I then matched those numbers up with the day's name column (i.e. Saturday gets the 1 column, Sunday the 2, etc...). Obviously, this would only work when you have a maximum of seven days, but that is all my report needs to do as it is a weekly payroll report. It's also great because if there is day that has no hours at all (meaning the query produced less than 7 date fields) the code just skips over assigning that day a ControlSource. All in all, it was a great solution for me.

Ben