Combine Rows based on 2 columns

[tomexcel1]

Hi All

Im quite new to Access and Im trying to figure out if this is even possible.

Basically I have a table in Access with over 1 million rows. I want to be able to combine rows that have the same person ID and the same item name.

This is a basic example of the current data:

Person ID	Item Name	Value
123	Item A	22
123	Item B	10
123	Item B	30
123	Item B	100

But want to get it so it combines identical person id and item. and sums the values like this:

Person ID	Item Name	Value
123	Item A	22
123	Item B	140

Is something like this even possible? Any help will be much appreciated.

Thanks
Tom

 

[Joe4]

This can be done pretty easily with an Aggregate Query. Just do the following steps:
1. Go into the Query Builder and select th table that holds this data
2. Add the Person ID, Item Name, and Value fields to the fields being returned.
3. Click on the Totals button icon shown in the menus (looks like a Sigma)
4. This will add a "Totals" row to the fields in the bottom of the Query Builder, with the words "Group By" under each one.
Under the "Value" field, change the words "Group By" to "Sum"

Now view your results in Datasheet View. You should now have what you want.