Generating ComboBox options from multiple tables (1 Viewer)

[Nuclear P0wers]

I currently have 3 different tables:

B Section
C Section
D Section

I want to figure out the SQL or Query required to have access look at the Table "C Section", find items in that table with the same number in a given field; look at the table "D Section" and list all items that have the same number in a given field, and combine those together to form a drop down menu query with only 1 field in it, that I will then use to populate a comboBox lookup list for the user to choose from.

To clarify, I am not trying to display linked fields in the same combobox, I want the combobox options to have a single selection option with all of the items from 2 different tables that have the same number in a given field.

Please help.

 

[Minty]

Hi and welcome to AWF!

Data, data, data.
Show us two sets of data what you have as a starting point, and what you expect as an output.
You can upload excel sheets or a small sample database

 

[Gasman]

@theDBguy Simplecsv function might do what you want.

Search this site for it.

 

[theDBguy]

Hi. Welcome to AWF!

Here's a link to SimpleCSV(), but I think maybe you're looking for a UNION query instead.

Code Snippets

 

[Nuclear P0wers]

Attached

 

[Pat Hartman]

Not sure why you have multiple tables with the same data. Sounds more like a spreadsheet design than a database. But you can use a Union query to populate the combo's Rowsource. Just keep a field that identifies the table the data came from so you know what table to open.

Bad design. It would be preferable to combine the like data into a single table.

 

[Gasman]

Hi. Welcome to AWF!

Here's a link to SimpleCSV(), but I think maybe you're looking for a UNION query instead.

Code Snippets

This is why I went with the function

combine those together to form a drop down menu query with only 1 field in it

 

[Nuclear P0wers]

Not sure why you have multiple tables with the same data. Sounds more like a spreadsheet design than a database. But you can use a Union query to populate the combo's Rowsource. Just keep a field that identifies the table the data came from so you know what table to open.

Bad design. It would be preferable to combine the like data into a single table.

I understand your concern, but i have simplified the data entry for ease of explanation. The two Section tables will have different data in them.

I guess my overarching question is this:

Can I create a query that only produces one field with records that contain field entries from 2 different tables?

 

[theDBguy]

This is why I went with the function

I see. Thanks for the clarification. Cheers!

 

[Pat Hartman]

Queries can join tables or queries if that is what you are asking. Open the query designer. Select the tables/queries you want to include. Then draw the appropriate join lines between the tables/queries. Select all the columns you want and add any selection criteria. The QBE will build the necessary SQL String do it's a point and click.

 

[Nuclear P0wers]

Queries can join tables or queries if that is what you are asking. Open the query designer. Select the tables/queries you want to include. Then draw the appropriate join lines between the tables/queries. Select all the columns you want and add any selection criteria. The QBE will build the necessary SQL String do it's a point and click.

Let's say that I have two different columns (fields) in a set of records. I need to combine the values listed in those two fields into one field for use in a drop down menu. Is there a way to do that? The two different fields in question have different names.

 

[Pat Hartman]

There are two different ways to combine data from multiple tables. You can use a union query which essentially stacks the data from two or more tables or queries. So if t1 has 10 rows and t2 has 5 rows, the union would produce 15 rows. The other method is a join. With a standard equi-join (equal match), the query will return one row for each matching set of rows. So given the same two tables and assuming two rows in the short table match rows in the long table, the join would return only two rows.

Sounds like you want a "stacked" solution so use a union query.

 

[Nuclear P0wers]

Sounds like you want a "stacked" solution so use a union query.

Awesome. I'll try this out and let you know if this works. Thanks!

 

[mike60smart]

Hi
Here is an example of your data and a Union Query.

 

[Gasman]

Am I missing something here?

I need to combine the values listed in those two fields into one field for use in a drop down menu.

The O/P has mentioned this several times, yet everyone is mentioning union queries, joins etc ?

Have I misread the post?

 

[mike60smart]

Let's say that I have two different columns (fields) in a set of records. I need to combine the values listed in those two fields into one field for use in a drop down menu. Is there a way to do that? The two different fields in question have different names.

It might help if you can show us what you expect to see

 

[Pat Hartman]

You have to read between the lines

 

[Gasman]

You have to read between the lines

Never been any good at that.
However when everyone else is promoting one thing and I another, then I wonder what I am missing?

 

[Nuclear P0wers]

Union Query worked guys. Thanks for your help!

 

[Pat Hartman]

You're welcome