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

Thanks Scott - I renamed the ID field.

So how do I select the multiple values?  Again sorry to be dense.

Can I use multiple combo boxes?  This is the way I had envisioned it:

Answer 3

First, I do not recommend accepting the defail name of ID for your autonumber PKs. I recommend naming your PKs tablenameID and then naming the corresponding FKs the same. This make it easier to remember and spot your relationships.

Second, you can use whatever view you want for your subform. It is typical that one uses a Continuous form or datasheet mode when you have multiple values.

Setting up the comboboxes is no different.

Answer 4

Thanks Scott - Sorry for being a bit dense here...I created this table:

And this relationship:

I looked at some tutorials and they all use a datasheet view for the subform whereas mine is single form.  Is it possible to use this methodology for singe forms?  If so, how would I set up the combo boxe(s) on the single form?

Thanks!

Answer 5

Wrong approach. 

"Now I need to have multiple possible corrective actions" This means you have a many to many relationship. One item can have multiple corrective actions and one corrective action can apply to multiple items. The proper way to model this relationship is with a junction table:

tblItemCARs

ItemCarID (PK Autonumber)

CorrectiveActionID (FK)

AssessmentSessionDetailID (FK)

Whenever you start numbering fields (CorrectiveActionID1, CorrectiveActionID2, etc.) You are creating a repeating group which is a violation of normalization rules.