Employee roster tables

[civicy2k]

Hi all,

I am trying to create this employee roster for work but I'm having trouble on how to setup the tables. Basically this is a monthly roster.
I have a Staff table holds Staff ID and Staff Name.
I am just not sure how to create the other table that holds the date and shift. Do I need to create a table with 28 columns for each date? (But every month has different number of days)

Staff ID | Staff Name | 02/01/2010 | 02/02/2010 | 02/03/2010 | 02/28/2010
123 | Mr. A | Shift A | Shift B | Shift C | Shift D
456 | Mr. B | Shift D | Shift C | Shift B | Shift A

Thank you for the help.

 

[DCrake]

NO:

You create a second table that has the following

ID:Autonumber
EmpID: Foreign Key back to the PK in the employee table
ShiftID: Foreign key back to the PK in the Shift type table
ActDate: Activity Date for the employee
Any other fields you need/want

David

 

[civicy2k]

DCrake,

Thanks for your quick respond.
Ok I did what you said, but how to create a report that will list the columns by date? If I insert the ActDate the column it will just list all the dates in 1 single column. Sorry if I misunderstood something. Thanks.

 

[DCrake]

Create a crosstab query and select the date field as the column heading.

David