Find Results From Two Different Tables

[dwichmann]

I have a large database with two distinct tables, one is for live orders and the other for archived orders. The first called TblOrder and the second called TblArchivedOrders. The field names in both tables are identical i.e OrderNumber, Customer, Product. I wish to write a query that will search through both tables and return any matching entries from either query. For example i would like to search for a customer called ABC Ltd and have it return any entries from TblOrders and TblArchivedOrders.

I do not know how to do this, please help

 

[dhookom]

You could combine the tables with a union query like:

SELECT tblOrder.*, "Active" As Status
FROM tblOrder
UNION ALL
SELECT TblArchivedOrders.*, "Archive"
FROM tblArchivedOrders;

Of course this depends on the actual spelling of tblOrder(s).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]