By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,656 Members | 1,892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,656 IT Pros & Developers. It's quick & easy.

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

P: 62
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.
Aug 7 '09 #1
Share this Question
Share on Google+
36 Replies


Expert 100+
P: 1,287
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.
Aug 7 '09 #2

P: 62
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?
Aug 7 '09 #3

Expert 100+
P: 1,287
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.
Aug 7 '09 #4

P: 62
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.
Aug 7 '09 #5

Expert 100+
P: 1,287
Perhaps you can use a UNION to get them all together.
Expand|Select|Wrap|Line Numbers
  1. SELECT id, name1 FROM myTable
  2.  UNION 
  3. SELECT id, name2 FROM myTable
  4.  UNION 
  5. SELECT id, name3 FROM myTable
Aug 7 '09 #6

P: 62
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.
Aug 7 '09 #7

Expert 100+
P: 1,287
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?
Aug 7 '09 #8

P: 62
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?
Aug 7 '09 #9

Expert 100+
P: 1,287
What's your SQL for the Union, in comparison to mine in Post #6?
Aug 7 '09 #10

P: 62
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?
Aug 7 '09 #11

Expert 100+
P: 1,287
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.
Aug 7 '09 #12

P: 62
You are wonderful!!!! It is working perfectly now. Thank you so much for your much needed help!!!!
Aug 7 '09 #13

Expert 100+
P: 1,287
You're very welcome.
Aug 7 '09 #14

P: 62
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?
Aug 7 '09 #15

Expert 100+
P: 1,287
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.
Aug 10 '09 #16

P: 62
No, all the duplicates have different ids. I just have to make it complicated. :)
Aug 10 '09 #17

Expert 100+
P: 1,287
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)
Aug 10 '09 #18

P: 62
That didn't work. I'm still getting duplicates.
Aug 10 '09 #19

Expert 100+
P: 1,287
Want to copy your SQL here and I'll look at it after lunch?
Aug 10 '09 #20

P: 62
Expand|Select|Wrap|Line Numbers
  1. SELECT ID, Modification_Number1 as modnum
  2. FROM [tbl:OASDHI]
  3. UNION
  4. SELECT ID, Modification_Number2 as modnum
  5. FROM [tbl:OASDHI]
  6. WHERE Modification_Number2 NOT IN (SELECT Modification_Number1 FROM [tbl:OASDHI]);
Aug 10 '09 #21

Expert 100+
P: 1,287
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
Aug 10 '09 #22

P: 62
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???

Expand|Select|Wrap|Line Numbers
  1. SELECT ID, modnum 
  2. FROM [qry:UnionModification1/2]
  3. UNION
  4. SELECT ID, Modification_Number3 as modnum
  5. FROM [tbl:OASDHI]
  6. WHERE Modification_Number3
  7. NOT IN
  8. (SELECT modnum FROM [qry:UnionModification1/2]);
Aug 10 '09 #23

Expert 100+
P: 1,287
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, ...
Aug 10 '09 #24

P: 62
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Min(ID) as ID, Modification_Number1 as modnum
  2. FROM [tbl:OASDHI]
  3. WHERE Modification_Number1 <> Null
  4. GROUP BY Modification_Number1
  5. UNION SELECT ID, Modification_Number2 as modnum 
  6. FROM [tbl:OASDHI] 
  7. WHERE Modification_Number2 NOT IN (SELECT Modification_Number1 FROM [tbl:OASDHI]);
Aug 10 '09 #25

Expert 100+
P: 1,287
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.
Aug 10 '09 #26

100+
P: 675
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.
Aug 10 '09 #27

Expert 100+
P: 1,287
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.
Aug 10 '09 #28

100+
P: 675
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.
Aug 10 '09 #29

P: 62
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT MinOfID, modnum
  2. FROM [qry:UnionModification1/2]
  3. UNION SELECT ID, Modification_Number3 as modnum 
  4. FROM [tbl:OASDHI] 
  5. WHERE Modification_Number3 
  6. NOT IN 
  7. (SELECT modnum FROM [qry:UnionModification1/2]);
Aug 10 '09 #30

Expert 100+
P: 1,287
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.
Aug 10 '09 #31

Expert 100+
P: 1,287
Now it's a circular query reference, I guess. Here's what to do, assuming I didn't make any more silly mistakes:
Expand|Select|Wrap|Line Numbers
  1. 'qry1
  2. SELECT Min(ID) as ID, Modification_Number1 
  3. FROM OASDHI GROUP BY Modification_Number1
  4.  
  5. 'qry2
  6. SELECT ID, Modification_Number2 
  7. FROM OASDHI 
  8. WHERE Modification_Number2 NOT IN 
  9. (SELECT Modification_Number1 FROM OASDHI) 
  10.  
  11. 'qry3 
  12. SELECT ID, Modification_Number3 
  13. FROM OASDHI 
  14. WHERE Modification_Number3 NOT IN 
  15. (SELECT Modification_Number1 FROM OASDHI)
  16.  AND Modification_Number3 NOT IN 
  17. (SELECT Modification_Number2 FROM OASDHI)
  18.  
  19. 'union1
  20. SELECT ID, Modification_Number1 FROM qry1
  21. UNION
  22. SELECT ID, Modification_Number2 FROM qry2
  23. UNION
  24. SELECT ID, Modification_Number3 FROM qry3
Aug 10 '09 #32

100+
P: 675
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.
Aug 10 '09 #33

P: 62
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?
Aug 11 '09 #34

Expert 100+
P: 1,287
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT ID, Modification_Number1, Modification_Number2, Modification_Number3 
  2. FROM OASDHI
  3. WHERE Modification_Number1 = Forms![myForm]!cmbModNumbers 
  4. OR Modification_Number2 = Forms![myForm]!cmbModNumbers 
  5. 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.
Aug 11 '09 #35

P: 62
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!!!
Aug 11 '09 #36

Expert 100+
P: 1,287
Glad to help. I've learned so much from this particular forum, it's good to know I can return the favor sometimes.
Aug 11 '09 #37

Post your reply

Sign in to post your reply or Sign up for a free account.