Combine Rows based on 2 columns

tomexcel1

New Member
Joined
Feb 22, 2018
Messages
40
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 IDItem NameValue
123Item A22
123Item B10
123Item B30
123Item B100

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

Person IDItem NameValue
123Item A22
123Item B140

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

Thanks
Tom
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,734
Messages
5,446,191
Members
405,390
Latest member
RafalKowalski

This Week's Hot Topics

Top