Sum Query

[frankt68]

Hi!

I'd like to know how to build a query to get the results I want. I'm using access 2010.

I have a table with the fields Group_ID, Item_ID, Item_Name and Quantity

Group_ID Item_ID Item_Name Quantity
1B.05.BB.01.017 64572 First Item Name 0
1B.05.BB.01.017 125733 Second Item name 906
1B.05.BB.01.017 144301 Third Item Name 2874



I'd like to build a query that will summarize the Quantity by Group_ID and show the Item_ID and Item_Name with the highest Quantity


Group_ID Item_ID Item_Name Quantity
1B.05.BB.01.017 144301 Third Item Name 3780


How can I do that?

 

[sneuberg]

Disregard this post.

You can do this with two queries as demonstrated in the attached database. The first query groups the Group_ID and gets the max of Item_ID. Note that Item_ID must be a number, not text, for this to work. Then you join that query back onto the table on the Group_ID and Item_ID fields.

 

[sneuberg]

Disregard my previous post. I didn't quite get it right.

 

[sneuberg]

Ok I think I have it this time. You can use Dmax in the criteria of the Quantity to get the records with the highest quantity and then display the sum of the quantities using DSum. This is demonstrated in the attached database where the table name is Item. The SQL looks like:

SELECT Item.Group_ID, Item.Item_ID, Item.Name, DSum("[Item]![Quantity] ","[Item]") AS [Sum of Quanity]
FROM Item
WHERE (((Item.Quantity)=DMax("[Item]![Quantity] ","[Item]")));

This will probably be ok if there are not a lot of records. If that's a problem let us know.

 

[frankt68]

Ok I think I have it this time. You can use Dmax in the criteria of the Quantity to get the records with the highest quantity and then display the sum of the quantities using DSum. This is demonstrated in the attached database where the table name is Item. The SQL looks like:

SELECT Item.Group_ID, Item.Item_ID, Item.Name, DSum("[Item]![Quantity] ","[Item]") AS [Sum of Quanity]
FROM Item
WHERE (((Item.Quantity)=DMax("[Item]![Quantity] ","[Item]")));

This will probably be ok if there are not a lot of records. If that's a problem let us know.

Thanx for your answer sneuberg and sorry for my late replay.

Unfortunately, your solution does not work correctly, because I get only one record displayed. However, with some modification of your first suggestion (which I didn't disregard) , I got what I wanted.
I changed the first query qryItemsGrouped to

SELECT Item.Group_ID, Sum(Item.Quantity) AS SumofQuantity, Max([Item].[Quantity]) AS [Max]
FROM Item
GROUP BY Item.Group_ID
ORDER BY Item.Group_ID, Sum(Item.Quantity);


and the second query qryFInalQuery to

SELECT Item.Group_ID, Item.Item_ID, Item.Name, qryItemsGrouped.SumofQuantity AS Quantity
FROM Item INNER JOIN qryItemsGrouped ON (Item.Quantity = qryItemsGrouped.Max) AND (Item.Group_ID = qryItemsGrouped.Group_ID)
ORDER BY Item.Group_ID;


So thanx for giving me an idea to get the solution I needed.