I want to convert an Excel spreadsheet that I use to track contractor time sheets to an access database. For payment at the end of the month each contractor completes a time sheet form on which they indicate every week’s total hours worked and this is captured by one person into the database. Profit values are calculated for each contractor per month by adding the hours worked for the 5 weeks for that month together and multiplying that total with a fixed profit rate for that specific contractor.
At present I have a table with contractor information linked (1-to-many) to another table with records for each contractor’s hour reports. Each hour report record contains 5 fields for the 5 week ending dates and the total hours worked for each of those weeks (mostly the 5th week segment is only a couple of days and not a full week).
Question 1: Should I rather create the time allocation table to show only one week’s hours per record; i.e. generate 5 individual records linked to each contractor’s primary key field where the week ending date would indicate which of the 5 entries it is for the month, or should I keep these time values in one single record for each contractor?
Question 2: How can I calculate the total hours worked for a specific contractor in a specific month so I can multiply that total with the contractor’s fixed profit rate value? In the Excel sheet I simply totaled the 5 cells and multiplied it with the rate cell’s value.
Your input would be greatly appreciated.
Thanks,
Mike