Access - AutoNumber Help

SantasLittleHelper

Board Regular
Joined
Nov 25, 2016
Messages
77
Is it possible to set a table up so that records are sorted into batch numbers automatically... maybe using AutoNumber?

E.g. I want the first 100 records to be Batch 1 and then the next 200 records to be Batch 2 and so on...
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
The entry form would have to post the batch# based on the ID when the record saves,
or
you can run an update query to convert all fields based on the ID.
 
Upvote 0
I wouldn't use AutoNumber for this purpose. You could quite easily have gaps in your Autonumbers (for example, if you start a new record, then exit it without saving, it will skip an Autonumber value), so there is no guarantee that if you go from 301-400 that there would be exactly 100 values in there.

It is typically advised not to use the Autonumber field in any sort of meaningful calculations, due to the unpredictability of the what the values may be.
 
Upvote 0
A table should be thought of as a bucket of records, conveniently laid out in row format using an applied sort. However, the only reliable way to sort a table of records is with a query. At least, that seems to be the conventional wisdom that I've read.

I don't think you can "set up" a table this way - probably because it's not supposed to be interacted with (plus the foregoing comment) thus would/should have no such need. Maybe more explanation around why this seems to be a requirement would help to arrive at a solution that works. I'm trying to think of a reason why a table of records would need to be grouped into 'batches' and so far, no luck.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top