Customized Autonumber

[Thinh]

I need to create an autonumber that is customized, it starts either with a q or p then it follow two digit which is the current year then it follows a 3 digit sequence number so it looks like something like this.
q06001 or p06001

The catch with this is when the year changes the three last digit will restart from 000. There is also another catch which makes it even harder is q and p has their own autonumber let me elaborate on it.
q06001
p06000

the next p autonumber will be p06001 and the next q autonumber will be q06002.
p and q have their own sequence.

my approach for this issue is concatenate them together.
p or q & year(date) & XXX. then i would have to create a recordset that would start from the button of the table that the autonumber is in. i would go from the bottom of the list and look for the first p or q depending on what i want the next autonumber to be. once i find what i am looking for i have to substring the last 3 digit out and add 1 and do the concatenation to create the new autonumber. I dont know if this is the best practise but this is what i have on my mind. if you have any suggestion or anything that might make things easier for me go ahead and throw at me. i have another solution is to store the latest p or q value in another table and keep updating that table but a table with 2 fields and 2 records kind of doesnt make any sense to me.

Any suggestion is more than welcome

thanks in advance for all the advice and suggestions.

 

[pbaldy]

Your proposed method is not the best (and you haven't accounted for year in your description of it). The more common method is a recordset or DMax that looks for the max number where p=p and year=year then adds 1. Your separate table idea is actually a decent one, particularly in a multiuser setting where there's a chance 2 people could get the same number by accident.