troubleshooting Question

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

Avatar of peispud
peispudFlag for Canada asked on
Microsoft Access
8 Comments2 Solutions59 ViewsLast Modified:

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")
    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")
        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
            Do Until rs_InnerLoop.EOF
                TotalQuantity = TotalQuantity + rs_InnerLoop![Quantity]
         ' 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: Set rs_InnerLoop = Nothing
    rs_OuterLoop.Close: Set rs_OuterLoop = Nothing

Open in new window

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 2 Answers and 8 Comments.
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>


Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 2 Answers and 8 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004