Relationship between a Calendar Table and Employee Table

[Guest]

Need Help:

I work at an office that has two shifts (A and B) that share 3 days a week
and alternate work days every other week. For example:

John Smith on the A Shift works Week 1 – Mon, Tues, Wed., Thurs and Week 2 –
Tues, Wed, Thurs, Fri.

Jane Doe on the B Shift works Week1 - Tues, Wed, Thurs, Fri. and Week 2 -
Mon, Tues, Wed., Thurs

So on Tues, Wed, and Thurs of every week both the A and B shifts work
together. On Fridays and Mondays only ½ the employees are there.

I have to create a daily report that tells me who is working on a particular
date.

I have two tables as follows:

(1) Roster containing the following fields: Name, EmployeeNo., Shift (A or B)
(2) Calendar containing the following fields: Date, Day of Week, A Shift
(yes or no), B Shift (yes or no)

I know I have to create a field to create a relationship between these two
tables, and I could do it if the A and B shifts were always split, but cannot
figure out how to create the field. Is this something I should use a subform
to create? I don’t know and any help would be greatly appreciated.

 

[Guest]

I would change the second table to normalize it:
tblCalendarShifts
==================
calDate date/time
Shift text (A or B)

You don't need the Day Of Week since it can be easily derived from the date
field.