I need to come up with a database for my organisation that basically does the following:
1) Allows selection of month and year - preferably from a dropdown - this could be say May 2006
2) This then opens to another form that allows selection of an employees name.
3) This then opens a third form that allows the input of hours data worked on a number of different services for that employee over the month selected. Different employees work on different days but have a fixed number of hours.
The purpose of this database is so we can provide reports that will allow us to show who worked at what service, when and the total number of hours for each service worked in a month or a quarter.
Trouble I'm having is deciding on the relationships and tables.
I have tblemployee
EmployeeNo - PK
ContractHours = No of hours the emploee works per week
DateID - PK
UnitID - FK
EmployeeNo - FK
EmployeeNo - FK
When the data is inputted it's going to be much easier for the inputter to select a month and year, then an employee - then fill in by tabbing across a form the hours for each date worked at each site.
I've posted a sample here of the timesheet that data will be inputted from if that helps make what I'm on about any clearer.
I guess what I'm struggling with is the date and how the relationships can be setup. One employee can work at any number of units in a month. One unit can have many number of employees working at it per month. I'm thinking I need a junction table for this but where does the date fit in? I'm an access newbie and I seem to have a mental block with this. Please help!