Merge two tables

[Guest]

I have 2 tables with the following fields:
Table1: customerID, shipdate, shipqty
data is ([1,5/5/05,50],[1,6/5/05,100])
Table2: customerID, returndate, returnqty
data is ([1,5/14/05,2],[1,6/5/05,3])

How can I merge this information into 1 table with the 5 different headings.
My new table should look like this.

CustomerID shipdate shipqty returndate returnqty
1 5/5/05 50 - -
1 - - 5/14/05 2
1 6/5/05 100 6/5/05 3

Note that if there isn't anything returned or shipped on a particular date,
that field should be null or zero. Queries I've tried will put values into
every field. For instance the data above with a select query would end up
being 4 records as follows:

CustomerID shipdate shipqty returndate returnqty
1 5/5/05 50 5/14/05 2
1 5/5/05 50 6/5/05 3
1 6/5/05 100 5/14/05 2
1 6/5/05 100 6/5/05 3

Please help.
TIA

 

[Guest]

Hi Francophone,

Try using the UNION ALL statement in SQL. Something like...

SELECT * FROM Table1 UNION ALL SELECT * FROM Table2

Chris