The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.
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.
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
LVL 7
Instead of a code solution, and assuming all other fields are identical, why not use an SQL SUM INSERT query to group on all fields, sum on quantity, and insert into a temp table for eventually replacing the source data?
The easiest way would be to create a new, empty table. This time add a unique index so duplicate records cannot be created. Then create an append query to summarize the data from the old table and append it to the new table. When you are certain the data transferred correctly, delete the original table and rename the new table.
Here's an example of a totals query that appends to a table.
INSERT INTO dbo_ExportDetail ( ProcedureCode, FromDate, ThroughDate, PAReqAmt, PAReqUnits )
SELECT e.ProcedureCode, e.FromDate, e.ThroughDate, Sum(e.PAReqAmt) AS SumOfPAReqAmt, Sum(e.PAReqUnits) AS SumOfPAReqUnits
FROM dbo_ExportDetail AS e
GROUP BY e.ProcedureCode, e.FromDate, e.ThroughDate;
To create yours:
1. Open QBE and select the old table.
2. Select all the columns
3. Change the query type to append and choose the new table.
4. Change the query to a Totals query by clicking the big sigma icon in the ribbon. Access will add Group By to all the columns.
5. Change "Group By" to "Sum" for the amount fields that you want to summarize.
6. Save and run.
Here's an example of a totals query that appends to a table.
INSERT INTO dbo_ExportDetail ( ProcedureCode, FromDate, ThroughDate, PAReqAmt, PAReqUnits )
SELECT e.ProcedureCode, e.FromDate, e.ThroughDate, Sum(e.PAReqAmt) AS SumOfPAReqAmt, Sum(e.PAReqUnits) AS SumOfPAReqUnits
FROM dbo_ExportDetail AS e
GROUP BY e.ProcedureCode, e.FromDate, e.ThroughDate;
To create yours:
1. Open QBE and select the old table.
2. Select all the columns
3. Change the query type to append and choose the new table.
4. Change the query to a Totals query by clicking the big sigma icon in the ribbon. Access will add Group By to all the columns.
5. Change "Group By" to "Sum" for the amount fields that you want to summarize.
6. Save and run.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
I see that. We probably started typing at the same time but I typed a longer answer so you got uploaded first.
Hi.
Wow!
I'm listening. I am scratching my head.
I know that you are correct. I will try/learn your suggestions. But, this how I help my wife in her business. For today at least, would prefer a more simple solution.
I only have to run the code once a year. (Around Jan 20)
I thank you for your replies.
Wow!
I'm listening. I am scratching my head.
I know that you are correct. I will try/learn your suggestions. But, this how I help my wife in her business. For today at least, would prefer a more simple solution.
I only have to run the code once a year. (Around Jan 20)
I thank you for your replies.
Our suggestions were essentially the same. Jeff gave you the executive summary and I gave you the detail steps of how to build the process.
Please try to implement my suggestion and let us know how you do.
Please try to implement my suggestion and let us know how you do.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.