query field to display results of fields from 2 different tables

[Guest]

I have a database that contains several tables (churches, community_centres,
party_rentals). Each table contains the same structure of fax numbers and
company names. I would like to create a query that would also contain a fax
number column. The records of this column should be one long combined list of
the fax numbers from all database tables.How do I do this.

My reason for doing this is to eliminate duplicate fax numbers from all the
tables combined by running the FIND DUPLICATES QUERY on this query.

Would this be the proper approach to eliminate duplicate fax records?

 

[Allen Browne]

A UNION query will combine the data from multiple tables into one long list.

Access can't show a UNION query graphically, so you will need to work with
the SQL statements. You could do it this way:

1. Create a query using the first table (churches).
Drag the fields you want into the grid.
Switch to SQL View (View menu).
Replace the semicolon at the end of the query with:
UNION ALL
Don't save: just leave this query open in the background.

2. Create a query on the 2nd table.
Drag the same field into the grid, in the same order.
Switch to SQL View.
Copy the SQL statement.
Paste it into the end of the first query.
Replace the semicolon at the end of the query with:
UNION ALL
Don't save: just leave this query open in the background.

3. Create a query on the 3rd table.
All 3 queries must have the same number of fields, in the same order.
Specify any sorting you want in this 3rd query.
Switch to SQL View.
Copy the SQL statement.
Paste into the previous query.

You should now have something like this:

SELECT CompanyName, Fax FROM Churches
UNION ALL
SELECT CompanyName, Fax FROM Community_Centres
UNION ALL
SELECT CompanyName, Fax FROM Party_Rentals
ORDER BY Fax;