Multiple combo boxes based on the same table

Question

I have a form with a combo box offering multiple selections of "Corrective Actions" (cboCorrectiveActionID) - options are based on the CorrectiveAction table. Each corrective action has a CorrectiveActionID. The CorrectiveActionID is being stored in my AssessmentSessionDetail table.

Now I need to have multiple possible corrective actions so I have created two more Corrective Actions combo boxes based on the same CorrectiveActions table.  I have added two more corrective actions lines to the AssessmentSessionDetail so now I have "CorrectiveActionID, CorrectiveActionID2 and CorrectiveActionID3. Note that the user may not use all three combo boxes.  Sometimes one, sometimes two and sometimes three corrective actions could be chosen.

The process works fine in that the three CorrectiveActionID numbers are being stored in my AssessmentSessionDetail table.  However if I create a query I need to add the CorrectiveAction table three times to the query in order to display the three possible choices.  The problem is that if I use that query in another query I get multiple duplicated records for the results.  I am thinking that each time there are one or two additional corrective actions added via the new combo boxes Access is duplicating the record.

I tried creating three separate queries, one for each possible selection, but the resulting recordset is 53,000+ lines vs the 104 lines that should be returned.

Where am I going wrong?

Answer 1

You might like to take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file provides brief and simple illustration of some of the basic operations in putting together a relational database.  If you go to the section on 'Building the user interface' and then click on the 'Next' button it illustrates a form for interfacing with the data in the tables described in the earlier sections of the demo.  You'll see that in the bottom right corner of the form is a subform for selecting multiple employers for the contact in the main form.  The subform is in continuous forms view, so instead of having three independent combo boxes as you have at present, three values would be selected by inserting three rows in the subform.  A subform in your form would be very similar to this.  At present in my demo no contact has more than two employers, but as few or as many per contact can be inserted.  The same would apply to your corrective actions.

Answer 2

Thank you Ken, your sample db DatabaseBasics was invaluable in solving this challenge!!

Rob

Answer 3

You are misunderstanding me. In a many to many relationship, you use a junction table where each combination represents one record. So if the Question can have multiple CARs, then you would have a record for each combination of the Question's PK and the CarID. 

For example. Let use the classic example of a student and courses. Each student can take multiple course and each course can have multiple students, Hence many to many. So lets say a student takes Math, English and Geography. The junction table would have THREE records. One for each course with the same studentID.