Auto ID Count

[andrewneal]

I am helping someone re-do their database and they would like the autonumber id to reset each year back to zero; something like yr&cnt (070001). This would need to be saved as the auto-generated id number for that data entry; again resetting automatically each 1 January. The next data entry will get the next number for that year (070002) and so on.

Oh, one last thing. There are 4 tables that this auto-generated date & count will need to work independently on. So, the first id in each table will be yynnnn; but will not affect the number sequence of any other table. Each table will track its own set of id numbers independent of any other table's results.

Any suggestions about the best way to go about this before I start that project?

 

[boblarson]

If they are using an autonumber for some meaningful purpose instead of just as a unique number then they are using an autonumber incorrectly. Since an autonumber is ONLY guaranteed to give you a unique number, not necessarily consecutive, then they should be generating their own number using DMax.

An autonumber can, all of a sudden depending on what happens, jump thousands of numbers, give you negative numbers, etc.

Read this for more info:
http://www.access-programmers.co.uk/forums/showthread.php?t=128935

Now, if they still want to chance it by using an autonumber like that, they can delete all records from the table and compact the database and it will reset the autonumber back to zero. But, I highly encourage them to create their own numbering system using DMax, if they want consecutive numbers and control over it.