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

[stateemk]

I have a db with a table that has many columns of info for various entities. In this table, there are three columns called, entity name, second entity name and third entity name. I need to combine all three columns into one drop box so a search can be done. Once the correct entity name is found, I need to be able to select it and bring up a form with all the data for that entity. For example, say there is an entity that has a name change so the new name becomes the entity name and the old name is the second entity name. If I search by the old name, I want to be able to select it and have all of that data come up in the form including the current entity name. I know this is probably very confusing so please help and let me know if I can clarify anything.

[ChipR]

What kind of search are you trying to do with a combo box? Would the user visually search through the data? This seems like something that could be done via DLookup, but more details are required.

[stateemk]

The search is just a form with one drop box that would only have all the entity names in it whether it be the first, second or third entity name. Once the user finds the name they are looking for, I could either have a button and do an OnClick command or just do an AfterUpdate command. After either command, another form would open with the rest of the data for that entity. Does this help?

[ChipR]

Did you try the combo box wizard and add your key field and the 3 name fields? The key field would be the bound column of the combo box and that would be the value that you use to filter the other form.
I think that AfterUpdate is good with a subform or other controls on the same form, but a button is preferable if you are going to pop up another form.

[stateemk]

I tried the combo box wizard, but it lays it out as a drop box with three separate columns. I want all the columns combined into one column.

[ChipR]

Perhaps you can use a UNION to get them all together.

SELECT id, name1 FROM myTable
 UNION 
SELECT id, name2 FROM myTable
 UNION 
SELECT id, name3 FROM myTable

[stateemk]

Sorry, forgot to mention that. I did a union and that got all the entities into one column, but then I ran into another issue. When I would click on any entity name that was in the second or third entity name column of the table, the subform that was opened would not populate with any data. If I would click on an entity that was in the first entity name column, it would populate the subform with all the data.

[ChipR]

There must be an issue with the key field then. Did you try keeping the key column visible in the combo box for testing or msgbox the filter value before opening the other form? The problem could be in the underlying query SQL, the combo box setup, or the code to show the record. If you have switched to a subform, are you setting the source or using Master/Child link fields?

[stateemk]

I think the union query that I have will work, but I can't figure out how to get an id field in there. There is an id field in the table that the entity names are coming from, but how do I combine that to the entity name in the union query?

[ChipR]

What's your SQL for the Union, in comparison to mine in Post #6?

[stateemk]

That does work. I hadn't looked at it closely enough, but now the problem is that there won't necessarily be something populated in every field, so there are a lot of blanks in the drop list. Also, how do I hide that ID field now?

[ChipR]

To hide the first column, change the Column Widths property of the combo box to
0";X"
The X being the width of the second column.

I think you can eliminate the Nulls just by changing the query to

SELECT id, Name1 FROM myTable WHERE Name1 <> NULL
UNION
etc.

[stateemk]

You are wonderful!!!! It is working perfectly now. Thank you so much for your much needed help!!!!

[ChipR]

You're very welcome.

[stateemk]

Okay, one more question with the same db. I have another column that I need to do the same thing with. It is a modification number column. There are four that I combined into one. There are duplicates in the columns though, so I just need the specific mod number listed once no matter which column it comes from in the table. I still need to have the id number in order to populate my table correctly. Any suggestions?

[ChipR]

If all duplicates have the same ID, then you can add the key word DISTINCT to the select query on your union. If not then...have to think about that.

[stateemk]

No, all the duplicates have different ids. I just have to make it complicated. :)

[ChipR]

Turns out you can't really do DISTINCT on one column, because the db engine doesn't know which row to return. This is terrible, but you may have to do something like:

Union1
SELECT id, mod1 as modnum FROM table
UNION
SELECT id, mod2 as modnum FROM table WHERE mod2 NOT IN (SELECT mod1 FROM table)

Union2
Select id, modnum FROM Union1
UNION
Select id, mod3 as modnum FROM table WHERE mod3 NOT IN (SELECT modnum from Union1)

[stateemk]

That didn't work. I'm still getting duplicates.

[ChipR]

Want to copy your SQL here and I'll look at it after lunch?

[stateemk]

SELECT ID, Modification_Number1 as modnum
FROM [tbl:OASDHI]
UNION
SELECT ID, Modification_Number2 as modnum
FROM [tbl:OASDHI]
WHERE Modification_Number2 NOT IN (SELECT Modification_Number1 FROM [tbl:OASDHI]);

[ChipR]

Ah, I found the problem. The NOT IN works for the second part of the UNION, but the first SELECT still gives duplicates. The way around that is:
SELECT Min(ID), Modification_Number1 FROM OASDHI GROUP BY Modification_Number1

[stateemk]

Here's the second union query I'm doing. I'm getting a parameter prompt for ID. I think it's because after I run the first query, instead of having ID as the header for the ID field, it is giving me an expr1000 header???

SELECT ID, modnum 
FROM [qry:UnionModification1/2]
UNION
SELECT ID, Modification_Number3 as modnum
FROM [tbl:OASDHI]
WHERE Modification_Number3
NOT IN
(SELECT modnum FROM [qry:UnionModification1/2]);

[ChipR]

It sounds like it's making up a column header. If you're using the Min(ID) in the first query, make sure you assign your own like:
SELECT Min(ID) AS ID, ...

[stateemk]

I tried that earlier and I get this message:

Circular reference caused by alias 'ID' in query definition's SELECT list.

. Here's the code I tried.

SELECT Min(ID) as ID, Modification_Number1 as modnum
FROM [tbl:OASDHI]
WHERE Modification_Number1 <> Null
GROUP BY Modification_Number1
UNION SELECT ID, Modification_Number2 as modnum 
FROM [tbl:OASDHI] 
WHERE Modification_Number2 NOT IN (SELECT Modification_Number1 FROM [tbl:OASDHI]);

[ChipR]

Try that same syntax but with the first GROUP BY query all by itself, then make a union using that query. I know it's an extra query, but it worked for me.

[OldBirdman]

I have been moving in this direction for some time, and been asking similar questions on this forum. The UNION queries kept getting worse, the problem more complex.

It is very simple to have a 2 column "SelectBox (listbox or combobox) to select by a name/title/desc field and use the hidden column to find the record and display it. There are a couple of methods for the display.

I can't post a solution at this time, but only express ideas until I get my own project working.

This is a database design problem. Having 3 "Entity Name" fields, some of which may be unused, is poor design. Moving just the alternate names to a separate table will help, but doesn't get rid of the UNION queries.

Move ALL the entity names to another table. This table at minimum needs 3 fields, Key, FK (Foreign Key), and Name. I also have a Type field, to keep track of whether this is Entity Name, or second ... or third ... SelectBox.ControlSource is now SELECT FK, Name FROM tEntityNames ORDER BY Name

If this list is very long, it can be easily filtered. A single CheckBox "Show All Names" could allow user to uncheck, adding a WHERE clause to the above WHERE Type=1 Other simple filters can be used.

[ChipR]

Good point OldBirdman. I assumed that this was a temporary fix, but really a redesign of the tables is required. It could be as simple as a Names table with entityID, Name, Type, where Type can be integer or string indicating primary, second, or third name option, or whatever.

[OldBirdman]

The tricky part is creating a new record. You create an entry in myTable and complete required entries. You cannot create the tEntityNames row yet, because you don't know the new PrimaryKey for myTable. If you save now, you have no tEntityNames name. If user goes to lunch now, this may become an orphan.

That is the part I'm working on now, but thought I would warn everyone before they leaped into it.

[stateemk]

This is a temporary fix. The db isn't actually mine, this is just what the customer wants. They don't want to pull out the entity name into a separate table. Now, I got my first two queries to run just fine, but I'm running into a problem with my third one. It won't every stop running. I may just have to create the table and the customer is going to have to deal with it unless there's something I'm missing in the sql.

SELECT MinOfID, modnum
FROM [qry:UnionModification1/2]
UNION SELECT ID, Modification_Number3 as modnum 
FROM [tbl:OASDHI] 
WHERE Modification_Number3 
NOT IN 
(SELECT modnum FROM [qry:UnionModification1/2]);

[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_number1 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.