Gaps in a Sequence Number

**Salad** · Jun 27 '08, 07:26 PM

Re: Gaps in a Sequence Number

jaka wrote:

Hi,
>
I'm in need of some expert help in sorting a numerical/alphanumerical
employee ID field (5&6 characters in length).
>
Also seperating the alphanumerical IDs and finding the gaps in the sequence
numbers (These numbers will always begin with a "C"). Reason for this is to
assign an unused alphanumeric number to a employee. Therefore only one
unused alphanumeric number needs to be viewed at a time. The original
database designer used the... DoCmd.GoToRecor d , , acLast to locate the
last record. It has become useless since I am at C99999. It is a 2003
database. Can this be done with Visual Basics? I will be glad to forward a
sample of the database.
>
Thanks
Jaka
>
>
Example:
>
Table: Employee
Field: EmployeeID
>
C1345
C0001
45573
100023
C1356
C1347
C1349
>
Sorted data;
C0001
C1345
C1347
C1349
C1356
>
Individual gap result;
C0002
C0003
C0004 etc....
>

The best way to sort a char/number field is to zero pad the numbers.
Otherwise you might get 1,10,100,2,20,2 00... It appears you do have it
zero padded so the sort should be OK.

The question is....is this id/key field linked to other records in other
tables? For example, let's say the current number is C1345. It really
should be C0004. If you have other records in say an orders table with
C1345, you would need to update all references of C1345 to C0004 in all
tables that use your key field.

Before you make ANY changes make sure you have a backup copy!!!!!

You could try this...if the Emp table doesn't have an autonumber. Open
the table in design mode and create a new field; NewNum type autonumber.
and NewEmpID type Text. Save it. You should now have a sequential list
of numbers in NewNum. With an update query you could update NewEmpID to
"C" & Right("00000" & NewNum,5)
Now you'll have a sequential list in the emp file.

There's a promblem with autonumbers in this type of situation. If you
open a record, start something, and then leave without saving the record
you'll have a skip between autonumbers.

So you need to change the autonumber to Numeric/longint so you don't get
gaps. So change NewNum to type Numeric.

For the most part, concatenating alph to numeric to create a key is poor
design, afaic. If you don't want breaks, you should create the key
when the record is saved, not when created. You can use DMax to get the
max number of NewNum and add 1 to it to avoid breaks. Ex:
NewEmpID = "C" & Right("00000" & (Dmax(NewNum)+1 ),5)
Also, once you update your table and get your dataentry correct for
creating the key you need to change NewEmpID to your old EmpID.

If your system has been in use for a while and other tables use your
current numbers, you have a problem that's not as easy to correct by
simply updating the number/key. Any help you get you should be willing
to pay for.

Juanica

http://www.youtube.com/watch?v=EAVJf5WMIEQ

**jaka via AccessMonster.com** · Jun 27 '08, 07:26 PM

Re: Gaps in a Sequence Number

Hi,

I appreciate your assistance. I ended using this statement to give me the
"one" unused number.

SELECT TOP 1 [EmployeeID]+1 AS [Unused Contractor Number]
FROM Employee
WHERE (((Exists (SELECT 0 FROM Employee AS i WHERE i.EmployeeID = Employee.
EmployeeID + 1))=False));

Though this barely fits what I wanted, it will work for now until I can
figure out something more robust.

Jaka

Salad wrote:

>Hi,
>>

>[quoted text clipped - 37 lines]

>C0003
>C0004 etc....

>
>The best way to sort a char/number field is to zero pad the numbers.
>Otherwise you might get 1,10,100,2,20,2 00... It appears you do have it
>zero padded so the sort should be OK.
>
>The question is....is this id/key field linked to other records in other
>tables? For example, let's say the current number is C1345. It really
>should be C0004. If you have other records in say an orders table with
>C1345, you would need to update all references of C1345 to C0004 in all
>tables that use your key field.
>
>Before you make ANY changes make sure you have a backup copy!!!!!
>
>You could try this...if the Emp table doesn't have an autonumber. Open
>the table in design mode and create a new field; NewNum type autonumber.
>and NewEmpID type Text. Save it. You should now have a sequential list
>of numbers in NewNum. With an update query you could update NewEmpID to
> "C" & Right("00000" & NewNum,5)
>Now you'll have a sequential list in the emp file.
>
>There's a promblem with autonumbers in this type of situation. If you
>open a record, start something, and then leave without saving the record
>you'll have a skip between autonumbers.
>
>So you need to change the autonumber to Numeric/longint so you don't get
>gaps. So change NewNum to type Numeric.
>
>For the most part, concatenating alph to numeric to create a key is poor
design, afaic. If you don't want breaks, you should create the key
>when the record is saved, not when created. You can use DMax to get the
>max number of NewNum and add 1 to it to avoid breaks. Ex:
> NewEmpID = "C" & Right("00000" & (Dmax(NewNum)+1 ),5)
>Also, once you update your table and get your dataentry correct for
>creating the key you need to change NewEmpID to your old EmpID.
>
>If your system has been in use for a while and other tables use your
>current numbers, you have a problem that's not as easy to correct by
>simply updating the number/key. Any help you get you should be willing
>to pay for.
>
>Juanica
>http://www.youtube.com/watch?v=EAVJf5WMIEQ

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200806/1

Gaps in a Sequence Number

Gaps in a Sequence Number

Comment

Comment