I have a database that I'm developing for reporting on where our care workers spend time working at our care homes . I have 3 tables as follows:
EmpID - PK
UnitID - PK
As there's a many-many relationship between the two - ie one employee can work at many units and one unit can have many employees working there - I have a juction table
EmpID - FK
UnitID - FK
Every month (stored in tblLink - Date field) the employees timesheets are filled into a form. But at any one month different employees will work at some but not all units and different units will have some but not all employees work there. When I fill in the timesheet data everything is getting updated in the table fields except even if an employee hasn't spend time at a unit - 0 hours need to be logged against it for that month (in tblLink - Hours field) or the queries and reports that calculate our costs for the month that are built against all this don't work. How can I get the blanks filled in? I'm also aware that I'll be getting an increasingly large set of tables here every month. There are 24 employees and 40 units. That's 960 entries per month. Help!!!
Thanks for any and all suggestions but please bear in mind I'm a novice - it's Access 2003,