one combobox, one query, 2 combined fields

[aft3rgl0w]

hey everyone, trying to figure out how to work this:
i have a form for where there is a Company field. This control is a
combobox.
there are 2 tables that both contain company names. what i want to do is
create a query that will combine the DISTINCT company names from both tables
into one list for the combo box. The one table is older and has most of the
company names already there... the other table is brand new so doesn't have
any of these entries, but I want entries from both tables to be shown in the
box.
Any help is much appreciated

 

[Dale Fye]

I've got to question why you would want to have two tables that contain
company name information? Doesn't sound like a very good database design.

To accomplish what you want, you will need to use a Union query, which would
look like:

SELECT [CompanyName] from Table1
UNION
SELECT [CompanyName] from Table2

If you just use the "UNION" identifier, it will remove all of the
duplicates. If you say "Union All" it will merge the contents of the two
tables.

HTH
Dale

 

[Duane Hookom]

Base your combo box Row Source on a union query. Something like:

SELECT FieldA, FieldB
FROM tblX
UNION ALL
SELECT Field3, Field4
FROM tblY;