P
Prochot
I'm having trouble figuring out how to design my tables and forms to
suite my purpose. I'm creating a crewing database to assign and track
employees and jobs over multiple shifts. I would like each shift to
be able to fill out a simple form assigning each job to an employee
and then be able to keep a history of what job each employee did on a
given date.
So far, I have four tables:
Employees:
EmplyeeID (PK)
LastName
FirstName
ShiftID (FK)
Jobs:
JobID (PK)
JobName
JobDescription
Shifts:
ShiftID (PK)
ShiftName
Crew:
Date
ShiftID (FK)
JobsID (FK)
EmployeeID (FK)
Employees.EmployeeID relates to Crew.EmployeeID
Jobs.JobID relates to Crew.JobID
Shifts.ShiftID relates to Crew.ShiftID
Shifts.ShiftID also relates to Employees.ShiftID
Employees are assigned to a shift but have the ability to work other
shifts.
I'm thinking this must not be right because I can't figure out how to
create a form listing each position that will fill the crew table.
Also, this would mean that each shift would generate 30 records in the
crew table each day (one for each job), meaning that after a few
months this table will be HUGE.
What am I missing here? What would be a better way to do this? How
would I setup the recordsource of the elements in the crewing form to
properly fill the table?
Any help would be really appreciated! Thanks!
suite my purpose. I'm creating a crewing database to assign and track
employees and jobs over multiple shifts. I would like each shift to
be able to fill out a simple form assigning each job to an employee
and then be able to keep a history of what job each employee did on a
given date.
So far, I have four tables:
Employees:
EmplyeeID (PK)
LastName
FirstName
ShiftID (FK)
Jobs:
JobID (PK)
JobName
JobDescription
Shifts:
ShiftID (PK)
ShiftName
Crew:
Date
ShiftID (FK)
JobsID (FK)
EmployeeID (FK)
Employees.EmployeeID relates to Crew.EmployeeID
Jobs.JobID relates to Crew.JobID
Shifts.ShiftID relates to Crew.ShiftID
Shifts.ShiftID also relates to Employees.ShiftID
Employees are assigned to a shift but have the ability to work other
shifts.
I'm thinking this must not be right because I can't figure out how to
create a form listing each position that will fill the crew table.
Also, this would mean that each shift would generate 30 records in the
crew table each day (one for each job), meaning that after a few
months this table will be HUGE.
What am I missing here? What would be a better way to do this? How
would I setup the recordsource of the elements in the crewing form to
properly fill the table?
Any help would be really appreciated! Thanks!