We help IT Professionals succeed at work.
Get Started

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

peispud asked
Last Modified: 2016-01-21

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

Watch Question
Distinguished Expert 2017
This problem has been solved!
Unlock 2 Answers and 8 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE