Increment number in a field

[mindjob]

Hello everyone,

I'm fairly new with Access. I'm designing a database to do some calculations that were previously done in Excel. We want to use Access for validation purposes (medical device industry). I'd like one field in a table to be able to increment a number by 1 each time a new row is added. I thought this could be done in the default value property for the field, but don't know how.

Thanks for your help,
Greg

 

[pbaldy]

The simplest way would be to give the field the Autonumber data type. Be advised that autonumbers will occasionally skip numbers, so you'd need to go a different way if that's a problem.

 

[mindjob]

Thanks Paul,

Would there be any way to start at a specific number and increment to that?
Greg

 

[MSAccessRookie]

Thanks Paul,

Would there be any way to start at a specific number and increment to that?
Greg

I have heard that there is a way using Visual Basic, but I do not have any references as to exactly how at this time.

I do remember, however, reading in this forum that someone suggested creating a table with 999 dummy entries, and going from there. Once entry 1000 is completed, the first 999 can be removed at will, leaving a table that starts at 1000. I know that this is kind of raw, but it will work if you have no other options.

 

[gemma-the-husky]

trying to manage a sequence without gaps is quite hard in Access - this is not realy what Access is about - Access (or any Database) actually deals in sets of data, where the sequential order is immaterial. A bit of a tautology, but in your case for example the sequence is valid only if sorted in that sequence order - otherwise it would just look like a random(ish) number.

Now you CAN impose (and store) a particular order, but if you then delete an item in the middle, you still get a properly sorted list, but now with gaps in the sequence. If so would you overcome this by renumbering every item or just leave it. If the former, thats spreadsheet thinking. If the latter (the number doesnt REALLY matter), then thats more like a database.

Hope this helps

 

[CraigDolphin]

Gemma is correct, but you can calculate gapless numerical sequences, on the fly, in queries....though it can be a time consuming task if there are large numbers of records in the underlying tables.

Accordingly, the score calculated for a given record may change if other records are deleted, added, edited, whatever.

One chap I helped recently was doing something quite similar on data in a table with over 65,000 records, which was joined to another table, and was a bit more complex than just a straight numerical sequence, but in the end it took about 15 minutes to run the query!

 

[Rabbie]

I need numbers in strict sequence to comply with legal requirements about Invoice numbers so I use the DMAX function to find the current highest nmber and then add 1 to it to generate the next number. It can be a little slow if you have a massive amount of records but is fine with under 10000 records. I suspect it can handle much larger tables as well without an unacceptable delay.