Removing Duplicates while keeping a running total

[Furnurgler]

High everyone I've had a look around the FAQ's and other threads but haven't been able to find what I'm looking for.

I have a table that contains two fields, one being 'part number' and the other being 'quantity'. Unfortunately there are multiple duplicate 'part numbers'. I am looking at keeping only one instance of the duplicate 'part numbers' but at the same time I want to add the 'quantities' together to keep a running total.

For example:

Before
Part Number Quantity
a1 1
a2 2
a3 2
a1 1
a2 1
a1 4
a1 1

After
Part Number Quantity
a1 7
a2 3
a3 2

Any help would be greatly appreciated.
Cheers

 

[aleb]

Looking at your example ( which is not a running sum ) you can do the following

SELECT Yourtable.[Part Number], Sum(Youtable.quantity) AS Quantity
FROM Yourtable
GROUP BY Table1.[Part Number];

 

[The_Doc_Man]

This is, I suspect, a case where things got oversimplified. If those were the ONLY two fields in the table, you could simply write the summation query as suggested by aleb, then write a make-table query deriving its data from the summation query, then run an erase query to empty out all the records in the original table, then finally write an append query to take that second table and append it back into the original.

HOWEVER, if that's all that is in your original table in the first place, it seems pointless. The solution offered by aleb is fine as-is.

So... is there more to this problem than meets the eye?

 

[Furnurgler]

Thanks for the responses guys. There are other fields related to the two mentioned above but I'm only interested the part number and quantity fields. Using Alebs suggestion it souldn't be too difficult to get the data I want. I'll give it a go and let you know.

Cheers
Georg

 

[Furnurgler]

Thanks

Thanks for the responses guys. There are other fields related to the two mentioned above but I'm only interested the part number and quantity fields. Using Alebs suggestion it souldn't be too difficult to get the data I want. I'll give it a go and let you know.

Cheers
Georg

 

[Furnurgler]

Thanks

Thanks for the responses guys. There are other fields related to the two mentioned above but I'm only interested the part number and quantity fields. Using Alebs suggestion it souldn't be too difficult to get the data I want. I'll give it a go and let you know.

Cheers
Georg