Batching Records in Access 2013 (1 Viewer)

kelesb22

New member
Local time
Today, 08:40
Joined
Nov 17, 2016
Messages
5


I'm very new at this and was hoping someone might be able to provide a code sample for me to work off of.
I have an Access db that gets a weekly import of 2,000 + records. These records need to be sorted by type then batched in groups of 50. The tables need to be updated with the batch id. Would I want to do a loop in VBA to update the top 50 records? Is there a way to do this in Macros or the SQL statements? I'm not finding any useful code samples online, this could be from my lack of knowledge of VBA. Any assistance would be much appreciated.

:banghead:
 

Ranman256

Well-known member
Local time
Today, 08:40
Joined
Apr 9, 2015
Messages
4,337
You don't really need vba.
You are correct, create a macro to run these queries.
Q1 pulls the top 50 recs without a batchID.
Q2 is an update query to fill in the ID of those in Q1.

A text box could be on the form to increment the batch id after Q2 runs.
Continue running until the DCount of Q1 is zero.
 

sneuberg

AWF VIP
Local time
Today, 05:40
Joined
Oct 17, 2014
Messages
3,506
Add to Ranman256's post in query Q1 you would limit the selection to fifty records by setting the Top Values property to 50. IN the SQL this would look like

SELECT TOP 50 ....
 

kelesb22

New member
Local time
Today, 08:40
Joined
Nov 17, 2016
Messages
5
Thank you both.

I have my Add and Update queries all set. Could you explain how I would have this run till Dcount is 0? Is there a setting in the Macro that will repeat this process until Dcount is 0?
 

Users who are viewing this thread

Top Bottom