The problem is, if you have a busy system, it is possible that you'll get duplicates using the suggestions already recommended. The best thing is to get everyone out of the system, find the last number used, then store that in it's own table. Then when you need a new serial number, you lock the SerialNo table, increment the number, get the new number, then unlock the table.
>Hi All:
>
>Consider the following table:
>
>
>Name Serial_Num
>Joe 001
>Blo
>Moe 002
>Curly
>
>
>For whatever reason, not every record has a serial_Num. I now want to add a record for 'Abe' that will contain the next serial_num, '003'.
>
>How can I retrieve the '002' with SQL similar to:
>
>
>Select bottom serial_num where !empty(serial_num)
>
>
>The problem is that there is no 'bottom' keyword. How do I do it?
>
>Thanks,
>
>Yossi
Craig Berntson
MCSD, Microsoft .Net MVP, Grape City Community Influencer