Many to Many

[HHush]

I'm setting up a volunteer database in which I will have events & shifts.
Event example: Hospitality Kitchen Shifts: Mon 1-3pm, Friday 3-5 PM

So what I want to do is set up an event and then choose which shifts the
event needs covered. In other words my shift table may have 25 shifts - but
eventA only is going to need shift1, shift2, shift15 covered.

Now for each shift I need set the number of volunteers needed, but the
default amount should be set up at the time of the event set up...

So when I set up the Hospitality Kitchen event, I'd state 10 vols needed -
by default.

Then I could edit the vols need by shift if I needed to.

Now the final piece is a I need to keep count of which shift is getting full
etc...

Love some guidance..

 

[Arvin Meyer [MVP]]

The first additional Table that you need is a EventShifts table

EventShiftID - PrimaryKey - autonumber
EventID - ForeignKey from the Events table
ShiftID - ForeignKey from the Shifts table

build a compound unique index on EventID and ShiftID. This will prevent
entering the same shift for the same Event.

Now your many to many side table will have the following fields:

EventShiftID - ForeignKey from the EventShifts table
EmployeeID - ForeignKey from the Employees table

Also create a compound unique index on both fields or you can select them
both and make them the Primary Key. I prefer using an autonumber as the
primary key, and the 2 fields as a unique index, as in the first table.