Question Listbox / Saving Multiple Primary Key Selections in a single Table Field

[padlocked17]

How do you take multiple selections in a listbox and save the Primary Keys associated with those listbox selections in one field on a table?

Basically, I've got a form that has a MemberID that populates the remaining data for that individual. I've got a Listbox that displays all of the awards they are eligible for. I want to be able to select multiple awards and have the Primary Key's for those awards saved in a single field in the tblMembers table.

That's what I need to accomplish and I'm open to alternative suggestions on how to accomplish it.

Right now I've got a tblAwards and tblMembers table. I've thought about creating a tblMembersAwards that would have a field for the MemberID and then a field for the AwardsID and could match them.

My problem there is that I don't know how to populate the listbox from the tblAwards table as well as that new tblMembersAwards table so that all awards are still displayed, but only the ones listed in tblMemberAwards are highlighted or selected.

Any thoughts or Ideas would be greatly apprecaited!

 

[DCrake]

Your second solution is the correct one. By having a member awards table using the PK of the member and the PK of the award in its own table is the normalised way.

Your issue about displaying the awards the member has in the listbox needs revising.

You form would have a combo box that lists the awards for the member and a list box of all the possible awards that can be awarded. However you could have a query in your list box to be a unmatched query, whereby list all in the awards table that do not exist in the member wards table based on the members id. Then if you want give the member another award then select one from the available list and update the tabel to include this and refresh the unmatched list.

 

[padlocked17]

I've given it a go, but I can't figure out how to actually filter the query based on the MemberID. I decided to go with 2 listboxes. One will have the results from the tblMembersAwards and the other will hopefully use the Exclude Query you mentioned and will look at what awards are not assigned to that person currrently and will allow you to add them.

Any ideas on how I can go about filtering by the MemberID on that exclude query?

 

[padlocked17]

Alright, I've run into a hitch. Currently I'm using the following SQL statement to display all of the awards applicable to the person:

SELECT tblMembers.MemberID, tblTracks.Name, tblAwards.Name, tblAwards.AllTracks, tblAwardCategory.AwardCategory
FROM (tblAwardCategory INNER JOIN (tblAwards LEFT JOIN tblTracks ON tblAwards.TrackID = tblTracks.TrackID) ON tblAwardCategory.AwardCatID = tblAwards.AwardCategoryID) LEFT JOIN tblMembers ON tblTracks.TrackID = tblMembers.TrackID
WHERE (((tblMembers.MemberID)=[Forms]![frmAddMember]![MemberID])) OR (((tblAwards.AllTracks)=True));

The SQL I've got for the Exclude Query isn't generating what I want by using the MemberID and is below:

SELECT tblAwards.AwardsID, tblAwards.TrackID, tblAwards.AwardCategoryID, tblAwards.Name, tblAwards.Description, tblAwards.AllTracks
FROM tblAwards LEFT JOIN tblMembersAwards ON tblAwards.AwardsID = tblMembersAwards.AwardsID
WHERE (((tblMembersAwards.AwardsID) Is Null) AND ((tblMembersAwards.MemberID)=[Forms]![frmAddMember]![MemberID]));

Any ideas?