Comparing two tables

[jcarstens]

I am having a tuff time figuring this out and I am sure it is super simple. I have a table which list sales and one which lists returns. These are related by a common order number. I would like to see a query of all order numbers, but only the items which have been returned.

My problem is it is replicating the returned line for every line of the original order number. I am attaching a sample database which depicts which I am trying to do. Query 1 shows the data in the wrong format. The screenshot below shows how I would like the output (with the struck out records to be blank)



Any help with this would be greatly appreciated.

[June7]

Need to do a compound join. Like:

SELECT tblSales.OrderNumber, tblSales.OrderDate, tblSales.Item, tblReturns.ReturnedDate, tblReturns.Item
FROM tblReturns LEFT JOIN tblSales ON (tblReturns.Item = tblSales.Item) AND (tblReturns.[Original Order Num] = tblSales.OrderNumber);

[jcarstens]

I have tried that code but that leaves my query with just the two records. I want to see all items purchased but only the returned item in the query. Referring back to my original screenshot, I want the same number (5) records with just the applicable returns showed.

[June7]

Then reverse the join:

SELECT tblSales.OrderNumber, tblSales.OrderDate, tblSales.Item, tblReturns.ReturnedDate, tblReturns.Item
FROM tblReturns RIGHT JOIN tblSales ON (tblReturns.Item = tblSales.Item) AND (tblReturns.[Original Order Num] = tblSales.OrderNumber);