many to many with a 'set'

[swtrader]

Given:

tblVols
pkVolID
VolFirstName
VolLastName
fkShiftGroupID
etc.

tblShifts
pkShiftID
ShiftName
ShiftStartTime
ShiftEndTime
etc.

tblShiftGroups
pkShiftGroupID
ShiftGroupName

(ShiftGroupName = "MainGroup" will have all 10 shifts - (A thru J))
(A Vol might be able to volunteer for only half a shift so a new Shift would be created (say Shift K). It would be used in a different group -- say ShiftGroupName = "JoesSpecialShiftGroup")


tblMtoMShiftPkg_Shifts
ShiftGroupAssignmentsID (autonumber but is not a key)
fkShiftID (lookup) (Composite Key)
fkShiftGroupID (lookup) (Composite Key)

----------------------------------------------

What I am trying to do is to relate all shifts in a group to a specific volunteer by selecting only the ShiftGroupName. So far, so good. HOWEVER, what i can't seem to get is how to add "Available" and "Assigned" Yes/No fields for each shift assigned to each volunteer. I have tried putting the fields in tblShifts (which clearly doesn't work) and I've tried putting the fields into tblShiftGroupsAndShifts.

I'm fairly certain that I have done this before in a different situation....creating a set of documents for each client and being able to set up a field that says whether the doc is in draft form, final form, or executed. I don't have access to that db and I can't remember what I did.

 

[lespaul]

So a volunteer can only select one shift and will always work that shift? That's the impression I get from having fkShiftGroupID in tblVols.

I would expect something like a VolunteerShift table in which you would have the volunteerID the ShiftGroupID and any information about that combination of volunteer and shift (like Available or Assigned).



Leslie

 

[swtrader]

Thanks for your response, lespaul. I've done a workaround whereby I append all 10 shifts to each volunteer through (a) and update query that updates a 'constant' table of only two fields: the pkVolID and the pkShiftID. The 10 shift IDs stay the same in that pre-append source table (1 thru 10). The pkVolID is updated from the displayed pkVolID on the VolsAndShifts form (which doesn't have any shifts -- yet). Once the source table is updated with the pkVolID, the Source Table is appended to the Many-to-Many table. It's a little clunkier than I wanted but it works.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.