merging tables using same composite keys

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have two tables--one for sales and another for returns.
Both have the same primary composite keys (ID, Month,
Date). They have many overlapping records--that is, there
are many occurences when there was a sale and return for
the same customer in the same month and year. I have no
problem getting a query to combine these records. I would
like to create a query that matches these records but that
also includes records that are not in common. In the case
where there are sales, but no returns I would like the
cells in the return column to be blank. I would like to
end up with something like this:

Cust ID Sales Mth Yr Returns Mth Yr
1 1000 01 2003 200 01 2003
2 2000 02 2003
3 500 03 2003 50 03 2003
4 200 04 2003

I can already get it to include records 1 and 3 because
they match. Record 2 has sales but no returns, and record
4 has returns but no sales. Is there a way to make it
display in these records this way? Any help would be
appreciated.

Jeff
 
Just off the top of my head, could you create a union query that would pull
all the records? I have not played with it yet, but it seems like that
would get you there.

Rick B


I have two tables--one for sales and another for returns.
Both have the same primary composite keys (ID, Month,
Date). They have many overlapping records--that is, there
are many occurences when there was a sale and return for
the same customer in the same month and year. I have no
problem getting a query to combine these records. I would
like to create a query that matches these records but that
also includes records that are not in common. In the case
where there are sales, but no returns I would like the
cells in the return column to be blank. I would like to
end up with something like this:

Cust ID Sales Mth Yr Returns Mth Yr
1 1000 01 2003 200 01 2003
2 2000 02 2003
3 500 03 2003 50 03 2003
4 200 04 2003

I can already get it to include records 1 and 3 because
they match. Record 2 has sales but no returns, and record
4 has returns but no sales. Is there a way to make it
display in these records this way? Any help would be
appreciated.

Jeff
 
Use a UNION Query with 2 parts. The first part is a Left Join from tblSales
to tblReturns which will returns all Sales and matching Returns if exist.

The second part is a Left Join from tblReturns to tblSales without matching
Sales Record.

Check Access Help for Union Queries.
 
Back
Top