Comparing 2 tables

[Amir]

Hi!
I have 2 tables:

tblReceiptEvents:
ReceiptNumber
ProductKeyInReceipt
Diameter
DeliveryPageNumber
QuantityInReceipt

tblDeliveryPagesEvents
DeliveryPageNumber
ProductInDeliveryPage
Diameter
QuantityInDeliveryPage

I want to find differences between the summed quantities of products in both
tables.

Restrictions:
1. Differences should be in "equivalent" delivery pages and receipts (means:
the records which are compared should have the same DeliveryPageNumber).
2. Query should treat products with different diameters as different
products (I don't want to compare 2" tube to 3" tube because these are
different products..).
3. In case a product appears only in one table ("null") I want to treat it's
quantity as 0 (NZ etc..).

You might simplify the question to the case where i'm looking only
differences to one of the tables, if it makes the solution easier. This is
good enough for me.

I've tried to solve that but got wrong answers one after another.. Maby too
much for my little brain..

I'll be greateful if you could help me..

Kind Regards,
Amir.

 

[Amir]

Here is one of my solutions that worked quite fine.

It has several problems:
1. I don't know how to make it compare only tables with the same
DeliveryPageNumber.
2. I don't know how to make dates restrictions using other fields named
"ReceiptDate" and "DeliveryPageDate" which are in the tables tblReceiptsData
and tblDeliveryPagesData which are 'linked' to the tables tblReceiptEvents
and tblDeliveryPagesEvents.

SELECT ProductKey, Diameter, MaterialType, SUM(SumQuantity) AS
DifferenceInQuantity
FROM [SELECT tblReceiptEvents.ReceiptNumber AS DocumentNumber,
tblReceiptEvents.DeliveryPageNumber AS DeliveryPageNumber,
tblReceiptEvents.ProductKeyInReceipt AS ProductKey,
tblReceiptEvents.Diameter AS Diameter, tblReceiptEvents.MaterialType AS
MaterialType, SUM(tblReceiptEvents.QuantityInReceipt) AS SumQuantity
FROM tblReceiptEvents
GROUP BY tblReceiptEvents.ReceiptNumber,
tblReceiptEvents.DeliveryPageNumber, tblReceiptEvents.ProductKeyInReceipt,
tblReceiptEvents.Diameter, tblReceiptEvents.MaterialType
UNION SELECT tblDeliveryPagesEvents.DeliveryPageNumber AS DocumentNumber,
tblDeliveryPagesEvents.DeliveryPageNumber AS DeliveryPageNumber
,tblDeliveryPagesEvents.ProductInDeliveryPage AS ProductKey,
tblDeliveryPagesEvents.Diameter AS Diameter,
tblDeliveryPagesEvents.MaterialType AS MaterialType,
(-SUM(tblDeliveryPagesEvents.QuantityInDeliveryPage)) AS SumQuantity
FROM tblDeliveryPagesEvents
GROUP BY tblDeliveryPagesEvents.DeliveryPageNumber,
tblDeliveryPagesEvents.DeliveryPageNumber,tblDeliveryPagesEvents.ProductInDe
liveryPage, tblDeliveryPagesEvents.Diameter,
tblDeliveryPagesEvents.MaterialType]. AS UnionAliasName
GROUP BY ProductKey, Diameter, MaterialType
HAVING (NOT SUM(SumQuantity)=0);


I know it looks frustrating but I will be grateful if you could help me..

Thanks and regards,
Amir.