Question Personalised Staff Rota

[trashbat]

I'm putting together the staff rota for the some graduation ceremonies and I want to ideally give each staff member a personalised rota showing what they are doing in each ceremony. I've currently got a table where each record is a ceremony and then each field corresponds to a particular task and is populated with a member of staff. Is there a simple way of doing this?

 

[spikepl]

Yeah. But not the one you outlined, which is not normalized because you are embedding data into structure and will get you into trouble very fast.

tblTasks
---------
TaskID PK
TaskName
TaskDescription


tblCeremonies
---------------
CeremonyID PK
CeremnonyName

tblStaff
--------
StaffID PK
StaffName


tblAssignments
---------------
AssignmentID PK
CeremonyID FK
TaskID FK
StaffID FK

 

[Trevor G]

Welcome to the forum,

If you have several tables perhaps one with the list of staff so you can relate that to a drop down (which then provides an easier manageable system for updating staff), then one for the ceremonies and what you need to place within it.

You can then create a query for the ceremonies and perhaps use some forms and reports to provide information for each staff member and there roles within the ceremonies.

 

[trashbat]

Thanks for that. I've already got a couple of other tables set up and a form with drop down menus for assigning the tasks but I hadn't thought of the assignments table. Sounds like that'd work to me. I'll let you know how I get on...thanks!

James

 

[trashbat]

I've got it all set up and working now with the database structure suggested. Thanks again!

 

[Trevor G]

Well done James and thanks for letting us know you have a working solution.