Totals Query - Sum Errors

[GoingCrazy]

I looked through the archives and couldn't find anything in specific terms so I thought I would just ask. I have a query that lists part number and quantities in inventory. Once I've supressed the duplicates I still have part numbers that are listed multiple times because of data in other fields. I want to sum the quantities so the part number is only shown once with a total quantity. I run a totals query with the part number "grouped by" and the quantity "sum". Other data in other fields are "grouped by" or eliminated from the query if they keep the record from being combined. I run the query and have found some quantities sum correctly and some are two or four times greater than they should be. Is there a way to keep this from happening?

As always, any help you can provide will be greatly appreciated.

Thanks.

 

[tlbroadbent]

You could use a subquery to do the summarization and then JOIN the other tables to the subquery. The following is an example that summarizes order quanties from and orders table and JOINS the query to the Part Inventory (PartInv) table to pick up the current inventory. This should give you an idea about how to do what you want.

SELECT rderID, rdTot, p.PartNo, p.InvQty
FROM [SELECT Orders.OrderID, Orders.PartNo, Sum(Orders.OrdQty) AS OrdTot
FROM Orders
GROUP BY Orders.OrderID, Orders.PartNo]. AS o
INNER JOIN PartInv AS p
ON o.PartNo = p.PartNo;
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[GoingCrazy]

Thanks Terry. I broke the operation into two queries and the second query seems to give me the right answer. I appreciate the help.