Generating ComboBox options from multiple tables (1 Viewer)

Nuclear P0wers

New member
Local time
Yesterday, 22:39
Joined
Dec 16, 2020
Messages
6
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

AWF VIP
Local time
Today, 05:39
Joined
Jul 26, 2013
Messages
10,355
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

Enthusiastic Amateur
Local time
Today, 05:39
Joined
Sep 21, 2011
Messages
14,048
@theDBguy Simplecsv function might do what you want.

Search this site for it.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:39
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

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

 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:39
Joined
Feb 19, 2002
Messages
42,981
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.
 

Nuclear P0wers

New member
Local time
Yesterday, 22:39
Joined
Dec 16, 2020
Messages
6
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:39
Joined
Feb 19, 2002
Messages
42,981
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

New member
Local time
Yesterday, 22:39
Joined
Dec 16, 2020
Messages
6
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

Super Moderator
Staff member
Local time
Today, 01:39
Joined
Feb 19, 2002
Messages
42,981
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.
 

mike60smart

Registered User.
Local time
Today, 05:39
Joined
Aug 6, 2017
Messages
1,899
Hi
Here is an example of your data and a Union Query.
 

Attachments

  • Union.zip
    24.1 KB · Views: 138

Gasman

Enthusiastic Amateur
Local time
Today, 05:39
Joined
Sep 21, 2011
Messages
14,048
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 ? :unsure:

Have I misread the post?:unsure:
 

mike60smart

Registered User.
Local time
Today, 05:39
Joined
Aug 6, 2017
Messages
1,899
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
 

Users who are viewing this thread

Top Bottom