Combine results from 3 different queries in to one

[mkc80]

I am trying to combine the results from 3 different queries in to one result table.


The Customer ID is the primary key in all 3 queries.

The structure of the 3 queries is as follows:
Query1: Customer-ID, product-1
Query2: Customer-ID, prodcut-2
Query3: Customer-ID, product-3

product-1, product-2, product-3 are all numeric fields which represent the count of product-x purchased by that customer.

I need a final table (union of these 3 queries) with the following columns: Customer ID, product-1 product-2, product-3

If some of the ID's are missing in any of the queries (meaning if its present in one query and not others), the product info should be set to 0.

Any suggestion on how to write a union query for this in MS Access?

[AlexHedley]

Have you tried using the Query Builder?

A basic Union Query is

SELECT FieldList FROM Table1
UNION
SELECT FieldList FROM Table2
UNION
SELECT FieldList FROM Table3

(You might need the UNION ALL)

Check out
http://office.microsoft.com/en-gb/ac...010206109.aspx