How do I combine three columns into one drop box in order to perform a search?

[ChipR]

I use completely unbound forms for record entry, and I find that it's really not that much more work. I just wait for the user to hit Save, then I validate all the fields and copy their values into an SQL Insert statement (or 2). I do use bound forms for viewing records, but I haven't found a need to both view and enter records on the same form.

[ChipR]

Now it's a circular query reference, I guess. Here's what to do, assuming I didn't make any more silly mistakes:

'qry1
SELECT Min(ID) as ID, Modification_Number1 
FROM OASDHI GROUP BY Modification_Number1

'qry2
SELECT ID, Modification_Number2 
FROM OASDHI 
WHERE Modification_Number2 NOT IN 
(SELECT Modification_Number1 FROM OASDHI) 

'qry3 
SELECT ID, Modification_Number3 
FROM OASDHI 
WHERE Modification_Number3 NOT IN 
(SELECT Modification_Number1 FROM OASDHI)
 AND Modification_Number3 NOT IN 
(SELECT Modification_Number2 FROM OASDHI)

'union1
SELECT ID, Modification_Number1 FROM qry1
UNION
SELECT ID, Modification_Number2 FROM qry2
UNION
SELECT ID, Modification_Number3 FROM qry3

[OldBirdman]

Yes, that is one way, if you don't need to enter multiple rows to the child tables when creating new entry. I only warned that this might not be a quick, EASY, fix, but another way to approach the problem.

stateemk has responded that he/she is going to continue with the 3 fields / one table, route.

[stateemk]

Okay, I'm to a point where I'm stuck again. I have all the modification numbers from four columns combined into one using a union query. Now, my issue is when I use the query results as the drop box items in the form. The user wants to be able to select one modification number and have it pull up all the entities with that specific modification number no matter which column that modification number is in on the main table. Does this make sense?

[ChipR]

In this case, you don't really need the ID saved in the combo box, because you'll have to look them all up with a query anyway, so you can eliminate that column from the individual and union queries. Which means you can change that first query to just select distinct modification_nu mber1 if you want.

Once a modification number is selected form the combo box, you'll list the results of a query like:

SELECT ID, Modification_Number1, Modification_Number2, Modification_Number3 
FROM OASDHI
WHERE Modification_Number1 = Forms![myForm]!cmbModNumbers 
OR Modification_Number2 = Forms![myForm]!cmbModNumbers 
OR Modification_Number3 = Forms![myForm]!cmbModNumbers

Or you can use a query with a parameter rather than reference the control on the form, but I don't know the syntax for that offhand.

[stateemk]

I do not know how to thank you enough for all your help ChipR. This is one of the first positive experiences I've had on a forum with reliable prompt answers. You've been a life saver for me. Thank you so much!!!

[ChipR]

Glad to help. I've learned so much from this particular forum, it's good to know I can return the favor sometimes.