SUMMING a selection of rows in a recordset

[prodtest]

Hello,
I am in need of a piece of code to help me a sum a selection of returned fields in a recordset. I already have the recordset sorted, but I am struggling with how to sum between rows.
The recordset ruturns a result similar to below:

product_code batch_number qty bin_location
68071 12256 10 D4
68071 12284 23 D4
68071 12297 2 D4

What I need to do is sum up the qty column into the highest batch numbered record.
So basically the qty's in batch numbers 12256 & 12284 need to be added to the qty already in batch 12297 and then the qty's in batchs 12256 & 12284, need to be set to zero.

I hope this had made sense.

I am self taught in VBA and this has kinda beaten me at the moment

Thanks
Ben

 

[Remou]

How about SQL?

SELECT prodtest.product_code, Max(prodtest.batch_number) AS MaxOfbatch_number, Sum(prodtest.qty) AS SumOfqty, prodtest.bin_location
FROM prodtest
GROUP BY prodtest.product_code, prodtest.bin_location