I am building a db to consolidate numerous reports currently built in Excel. Almost all of these reports are based on two spreadsheets updated weekly (employees turn in time once per week - not daily). The spreadsheets list many details including the consultant, the client site, the account manager (for commisions to be paid), how many hours are worked each week, the invoice rate we charge the clients and hourly payrate. I have tables built for employees, clients, pay, etc... but I am unsure how to build the hours/week table. Do I need 52 seperate columns for each week of the year? Or is there a better way to build this table?
The attached spreadsheet shows a glimpse of the detail I need. Obviously (for normalization), the empID and clientID would pull from those appropirate tables. The hours table (or whatever name is appropriate) should reflect every client-billable hour worked for each week of the year. I can break down per day if neccessary (I can see benefits both ways as 99% of time clients only care about weekly totals. However, for long-term planning, it would be nice for employees to enter time daily).
* A FEW NOTES*
- Consultants can work at multiple locations in the same week.
- Consultants can have two different invoice rates for two different clients
- Consultant pay rate does not change/client. It may change once a year (stored historically in pay table)