Linking Tables

Con · September 22nd, 2003, 11:36 PM

I have two tables that I am trying to link together. Table "A" has purchase records for year 2002 and table "B" has purchase records for 2003. Table "A" has some customer name & numbers that Table B does not have and vise versa. I need to link both tables (listing all the customers from BOTH table only once) and compare the items the customer purchased BEFORE December 31st. 2002 (in one column) and AFTER December 31st. 2002 (another column). The list of items have to include all purchases - old, new, or repeat purchases - for the BEFORE OR AFTER. The items are represented by code numbers. I want to have a table that I can review what each customer (new/old) is purchasing.

Table "A" Year 2002
Customer Name Number Items
Jane 112 1789
Jane 112 23885
Sam 399 2390
Eric 272 4711
Eric 272 3003
Eric 272 2878
Sandy 347 3003
Sandy 347 2390

Table "B" Year 2003
Customer Name Number Items
Ellen 561 3003
Jane 112 23885
Jane 112 4277
Jake 288 2878
Jake 288 1187
Eric 272 4066
Sandy 347 2390

The table that I want is this:

Customer Name Number Year 2002 Year 2003
Jane 112 1789 23885
                               23885 4277
Sam 399 2390
Eric 272 4711 4066
                               3003
                               2878
Sandy 347 3003 2390
                               2390
Ellen 561 3003
Jake 288 2878
                                            1187

Can someone kindly show me how to get the result that I wanted. Thanking you in advance for your kind assistance.

alung · September 23rd, 2003, 06:04 AM

Hi Con,

what you could do is create a temp tbl with a col for the year.
append all data from tbl A to temp with year 2002 and then do the same for tbl B (year 2003) then use a crosstab query on the temp tbl (group by cus name & number)

hth
alun

Steven · September 24th, 2003, 04:32 PM

Another possible option would be to make a union query that selects the people, eg:

Code:

SELECT [tblA].[Customer Name] FROM [tblA]
UNION
SELECT [tblB].[Customer Name] FROM [tblB]

This way you wouldn't have to have a temporary table.

Basically, if you can select the info you need from one table, you can union it with the same sort of thing from the other table

Steven

I am a loud man with a very large hat. This means I am in charge