By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,289 Members | 1,463 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,289 IT Pros & Developers. It's quick & easy.

Gaps in a Sequence Number

P: n/a
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.GoToRecord , , 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....

Jun 27 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.GoToRecord , , 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,200... 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

Jun 27 '08 #2

P: n/a
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,200... 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.com
http://www.accessmonster.com/Uwe/For...ccess/200806/1

Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.