asked on

MS Access Trouble With Grouping and Suming Query Results

I am having trouble trying to figure out how to Sum the totals of a Query.

The two tables in use are "Products" and "ProdLocations"

A ProductID can have many ProdLocIDs.

I need to show all the productdIDs and the ProdLocIDs that match. This is working. So sometimes in the query it may show two record lines such as

ProductID | ProdLocID | QtyLoc
__________________________________
3349 | 1678 | 5
3349 | 1524 | 6

But I need the query to give me 1 single record line showing QtyLoc with a value of 11

How can I accomplish this?

Below is my query info. Thanks for the help!

SELECT Products.ProductID, Sum(Nz(ProdLocations.QtyLoc,0)) AS SumOfQtyLoc, ProdLocations.dtmAdd, ProdLocations.dtmEdit
FROM Products LEFT JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID
GROUP BY Products.ProductID, ProdLocations.dtmAdd, ProdLocations.dtmEdit;

Open in new window

PatHartman

A totals query summarizes to the combination of unique values specified in the Group By. The picture of the output was created by a different query than what you posted. Based on the query picture, remove all but the first two columns.

Also, unless you need something from Products in the resultset, you do not need to include that table in the query at all since ProductID is in the ProdLocations table.

Dustin Stanley

ASKER

Also, unless you need something from Products in the resultset, you do not need to include that table in the query at all since ProductID is in the ProdLocations table.

Thanks. I don't 100% yet if I need it but mostly likely not.

Ok let me get really specific and I narrowed it down to a certain product for this example. This should be a good example to use.

ProductID 9345

Query View:

Query Results:

SQL:

SELECT ProdLocations.ProductID, Sum(Nz(ProdLocations.QtyLoc,0)) AS SumOfQtyLoc, ProdLocations.dtmAdd, ProdLocations.dtmEdit
FROM ProdLocations
GROUP BY ProdLocations.ProductID, ProdLocations.dtmAdd, ProdLocations.dtmEdit
HAVING (((ProdLocations.ProductID)=9345));

Open in new window

I need for it to be one record line showing

ProductID | SumOfQtyLoc
____________________________
9345 | 12

Sorry about the confusion. I got ahead of myself. Thanks Pat for the help.

Dustin Stanley

ASKER

Also, unless you need something from Products in the resultset, you do not need to include that table in the query at all since ProductID is in the ProdLocations table.

I reviewed over everything and I do need it.
But I am still struggling with the part below.

I need for it to be one record line showing

ProductID | SumOfQtyLoc
____________________________
9345 | 12

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dustin Stanley

ASKER

The query string you posted takes ProductID from Products and nothing else from the Products table. Since ProductID is in the location table, you don't need Products at all.

This a step in a larger query. So I have to pass ALL the ProductIDs from the Product Table. That's the reason why I have Sum(Nz(ProdLocations.QtyLoc,0))

Where there are Nulls it places 0 for me to calculate later down the Query road.

Dustin Stanley

ASKER

PERFECT! Thanks!