What Type of Query do I Need?

[Vetter]

I have 2 tables that have sales data, one for 2002, one for 2003.

I would like to combine them to have all the data in one table. The format needs to be like:

Cust, Item, 2002, 2003.

If I have a cust, item that was purchased in both 2002, and 2003 will this show up in the same row? If the customer bought just one of the two year's will this show? ie

Cust, Item, 2002, 2003
A ,A, 1,2
A,B,1,
A,C,,2

There may be cases where the customer only exists in one of the years. Ie. They bought only in 2002 or they only bought in 2003.

Do I need a union query?

Thanks,
vetter

 

[Rich]

You don't need separate fields for each year, just a field that shows the year/date of purchase

 

[Vetter]

Ideally yes, but this data already comes from a data source that is in that format. I can't change the format of this legacy system.

 

[Mile-O]

Ideally yes, but this data already comes from a data source that is in that format. I can't change the format of this legacy system.

UNION two queries then.

i.e.

SELECT A, B, 2003
FROM MyTable
UNION
SELECT A, B, 2004
FROM MyTable;