Duplicate Qty in query

[odrozd]

I have two tables, Orders and Sales. Product Description is common to both tables. I am running the following query;

SELECT DISTINCTROW Orders.ProductDesc, Sum(Orders.quantity) AS OTotal, Sum(sales.Quantity) AS STotal
FROM Orders INNER JOIN sales ON Orders.ProductDesc = sales.ProductDesc
GROUP BY Orders.ProductDesc;

The results give twice the amount expected.

Can some one enlighten me.

Thanks
Otto Drozd

 

[pr2-eugin]

Otto, What is twice the amount? The number of records? The amount calculation? Could you show some sample data?

 

[odrozd]

The Amounts on the tables are numeric.
If the table shows:
Purchase
Product-A, PQty 12
Product-B, PQty 12
Product-A, PQty 13
Product-B, PQty 10
Sales
Product-A, SQty 10
Product-B, SQty 10
Product-A, SQty 10
Product-B, SQty 10

The results of the query are:
Product-A, SQty 40 PQty 50
Product-B, SQty 40 PQty 44

Hope this will Help

Thanks Otto

 

[pr2-eugin]

Why are you not joining on the OrderID but on the Product Description?

 

[MSAccessRookie]

The Amounts on the tables are numeric.
If the table shows:
Purchase
Product-A, PQty 12
Product-B, PQty 12
Product-A, PQty 13
Product-B, PQty 10
Sales
Product-A, SQty 10
Product-B, SQty 10
Product-A, SQty 10
Product-B, SQty 10

The results of the query are:
Product-A, SQty 40 PQty 50
Product-B, SQty 40 PQty 44

Hope this will Help

Thanks Otto

Your problem exists because you are Using a duplcated item as a Primary Key. Each Sum will be performed for each instance of 'Product-A' or 'Product-B', regardless of whether it is a Sales Quantity or a Purchase Quantity. To Eliminate this, you must distinguish between the two.

As pr2-eugin pointed out, a common key (like an orderID) could work for you. Another alternative would be to modify the Table to add a Field that will identify the Transaction Type, and then to use a compound key. If you then joined the Tables on the ProductKey and the Transaction Type the duplication would go away.

 

[jdraw]

You should research Normalization. There is a link within the article at this site
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452


Getting your Table and relationships set up properly is key to successful data base.

 

[odrozd]

Thanks Guys you are right, it does not pay to take short cuts.
The Final query is as follows ans it works fine.

SELECT Products.ItemCode, Products.BarCode, Products.VendorCode, [Orders Totals Query].ProductDesc, [Orders Totals Query].OrdersTot, [Sales Total Query].SalesTot, [OrdersTot]-[SalesTot] AS AQty, [AQty]*[AvgOfPPrice] AS InvCost
FROM (Products INNER JOIN [Sales Total Query] ON Products.ProductDesc=[Sales Total Query].ProductDesc) INNER JOIN [Orders Totals Query] ON Products.ProductDesc=[Orders Totals Query].ProductDesc;