Relationship between a Calendar Table and Employee Table

P

punkyfire

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.
 
D

Duane Hookom

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top