Wish to consolidate records which are identical except for [Quantity]

Hi.

At the beginning of each  year, we do an inventory count.  Unfortunately,  multiple entries for the same [StockNum]  happen.

But for archival purposes,  I would prefer to only have one record for each [StockNum].   The following code recognizes when multiple  entries for [StockNum]  happen.   It works!  It recognizes the multiple entries for [StockNum]  for inventory purposes.  It adds up the [Quantity] field and stores it in a varaible... but the code below does nothing beyond this.

My question.. I need to replace multiple records with one record.  All records are identical except for   [Quantity].    The replacement record will be identical to the others except that [Quantity] = sum([Quantity])
I really am not sure of the most efficient way to do this.

Dim dbs As DAO.Database:     Set dbs = CurrentDb
    Dim rs_OuterLoop As DAO.Recordset: Dim rs_InnerLoop As DAO.Recordset
    Dim TotalQuantity As Long
    Set rs_OuterLoop = dbs.OpenRecordset("Select * from [tbl Transactions] where [Source] = 200 and [Destination] =1")
    rs_OuterLoop.MoveFirst
    Do Until rs_OuterLoop.EOF
        Set rs_InnerLoop = dbs.OpenRecordset("Select * from [tbl Transactions] where [StockNum] = " & rs_OuterLoop![StockNum] & " and [Source] = 200 and [Destination] =1")
        rs_InnerLoop.MoveLast
        If rs_InnerLoop.RecordCount > 1 Then
            ' Copy entire record here. Later in the process,  the record will be saved / modified (only [Quantity] will change.
            TotalQuantity = 0
            rs_InnerLoop.MoveFirst
            Do Until rs_InnerLoop.EOF
                TotalQuantity = TotalQuantity + rs_InnerLoop![Quantity]
                rs_InnerLoop.MoveNext
            Loop
         ' The 10 records are all identical... Only [Quantity] changes per record.
         ' I now wish to have just one record.  [Quantity] = TotalQuantity
        End If
        '
    rs_InnerLoop.Close
    rs_OuterLoop.MoveNext
    Loop
    rs_InnerLoop.Close: Set rs_InnerLoop = Nothing
    rs_OuterLoop.Close: Set rs_OuterLoop = Nothing

Select all Open in new window