unique values query from multiple tables (1 Viewer)

[jeffhunter]

Hi,

I am working on a query that should result in single occurences of a date from multiple tables.

Ex.

tbl_orders_from_web has field "date"
tbl_orders_by_phone has field "date"
tbl_orders_by_fax has field "date"

I am trying to create a query that will give me unique value dates from all three tables.

Dates should only appear once. A particular date may only appear in one table but should show up in the query. the same Date may appear in multiple tables but should only result once in the query.

Any ideas?

 

[David R]

A UNION query will get all your tables' dates into one query field, where you can then use Unique Values to make sure you only have one example of each date.

 

[jeffhunter]

Hi,

I forgot to add another aspect.

Say that from each table, I only want to view a particular type of order (say by product category). What other items do I have to add to the union query?

Jeff

 

[Fornatian]

I don't know if other users approach this the same but under those circumstances I always build two separate queries as sources for the union query suchas:

Qry1: Select * from Table1 Where MyField=1
Qry2: Select * from Table2 Where MyField=1
Qry3: Select * from Qry1 UNION Select * from Qry2

This example is obviously assuming that both have same no of fields and data types.

 

[Rich]

Why do you have three tables for orders?