Question Complex attendance tracker

[lightkeepr]

26 crossing guards that can have up to 3 default scheduled shifts a given day.
6 crossing guards fill in as a substitute if a scheduled guard cannot work a given shift
If a sub fills in I need to be able to remove the shift from the scheduled guard and log that shift to the subbing guard
30 different crosswalk locations across 6 different schools, some crosswalks have more than 1 guard present
If a single school is out the shift need to be removed from the default schedule for all crosswalks related to that school.


Payroll is done bi-weekly and I want to keep a history of everyone's attendance.



This has been being manually tracked in excel and it is getting out of control and taking WAY too long. any suggestion on how to move forward in creating a db that can achieve this?







I already have the following:
Table: Guard Info
FirstName
LastName
CellPhone
HomePhone
HireDate
Seniority
Email
Shift1
Shift2
Shift3


Table: School Name
School name


Table: Crosswalks
Crosswalk Location


Table: Full Crosswalks
School (from School Name table)
Location (from Crosswalks table)
Morn/Aft
ShiftWeight (each shift is usually 1 hour, some are 2)

 

[lightkeepr]

I am kicking this around with my limited knowledge of access and I have a question:
I have a table that lists shifts and a weighted value associated with it. Is it possible in another table (attendance days) that if a shift is selected from a lookup refernce, the weighted value can be automatically placed in the attendance days table in a field of it's own?