Calendar Table and Roster Table Relationship Recommendations???

[punkyfire]

I work in an office that has an A shift and a B shift. 3 days a week, the A
and B Shifts work together. For Example:

John Smith on Shift A works alternating weeks Mon., Tues, Wed, Thurs. and
Tues, Wed, Thurs, Fri.

Jane Doe on Shift B works alternating weeks Tues, Wed, Thurs, Fri and Mon.,
Tues, Wed., Thurs.

(1) I've created a table that has calendar for the year 2008 with the
following fields Date; Day of the Week; A Shift (yes or no); B Shift (yes or
no).

(2) I have a Roster Table that contains personnel information in the
following Fields: Name, EmployeeNo, Shift (A or B).

I need to create a query or report that gives me a daily report that tells
me who is working on each individual date of the year and I know I need to
create a field that these two tables can share, but am at a loss as to how to
connect them.

Any ideas or questions for clarification

 

[FredFred]

1. Does the list keep changing regarding who is in the A shif and B shift?

2. Yo need to decide .....are you mostly interested in showing "A/B" or
individual's names?

3. Is the "Name" entry in the db just a name or does it have a lot of other
data associated with it?

4. Is each employee always in only one shift (A or B)

The result will vary based on the answers. Let's say that the answer to
#1-#3 is the most complicated possibility, but #4 is "yes, only one shift"
Then here would be an idea:

Make a shift scheduling table which has a record for each instance of a
shift being scheduled to work a day. Two fields.....the date and the shift.
Two shifts on one day = two records

Make a table of employees, and put a field in for their current shift
designation (A or B).

Link them on the shift designation. In reports & forms group by the date.

Hope this might be useful