Need to detect missing Alpha Numeric numbers

[Randy]

I have found a way through this newsgroup of finding and detecting missing
numbers in a series of numbers. Using a numbers table with all the possible
numbers that would be used etc. The situation is I have certificates that
have alpha numeric numbers. Example: S1000021, X123232, P12345 and some
may only have numbers such as 46765. I'm trying to figure out a way to
track missing or gaps in certificate numbers in the database, even though
they are usually alphanumeric but not always. Any ideas...Thanks. Randy

 

[John Vinson]

I have found a way through this newsgroup of finding and detecting missing
numbers in a series of numbers. Using a numbers table with all the possible
numbers that would be used etc. The situation is I have certificates that
have alpha numeric numbers. Example: S1000021, X123232, P12345 and some
may only have numbers such as 46765. I'm trying to figure out a way to
track missing or gaps in certificate numbers in the database, even though
they are usually alphanumeric but not always. Any ideas...Thanks. Randy

What constitutes "missing"???

If you have certificates S1000021, T1000021 and V1000021, is U1000021
missing? How about U31237890?


John W. Vinson[MVP] ]

 

[Randy]

Certificates are forms assigned to employees, with various series of
numbers. The employee issues the certificates to our customers. The
employee turns in our copies of the certificates to the office where they
are entered into the database. Since these are government accountable
certificates, we must account for every single certificate. When the
certificates are entered into the database, I need a way to determine who
the certificates were assigned to and if any certificate is missing. Perhaps
an employee lost a certificate, etc. An Employee is assigned Certificates;
D10000 to D100024. If D100003 is missing or lost I need to know...Thanks

I have found a way through this newsgroup of finding and detecting missing
numbers in a series of numbers. Using a numbers table with all the
possible
numbers that would be used etc. The situation is I have certificates that
have alpha numeric numbers. Example: S1000021, X123232, P12345 and some
may only have numbers such as 46765. I'm trying to figure out a way to
track missing or gaps in certificate numbers in the database, even though
they are usually alphanumeric but not always. Any ideas...Thanks. Randy

What constitutes "missing"???

If you have certificates S1000021, T1000021 and V1000021, is U1000021
missing? How about U31237890?


John W. Vinson[MVP] ]

 

[John Vinson]

Certificates are forms assigned to employees, with various series of
numbers. The employee issues the certificates to our customers. The
employee turns in our copies of the certificates to the office where they
are entered into the database. Since these are government accountable
certificates, we must account for every single certificate. When the
certificates are entered into the database, I need a way to determine who
the certificates were assigned to and if any certificate is missing. Perhaps
an employee lost a certificate, etc. An Employee is assigned Certificates;
D10000 to D100024. If D100003 is missing or lost I need to know...Thanks

So there would be 90024 certificates issued, D10000, ..., D99999,
D100000, D100001? Or was that a typo? What about other letters?

You'll need, I suspect, a table of all the certificates originally
issued. You can use the Unmatched Query Wizard to find all
certificates which are in this table which are NOT in the employee
submission.

Unless you have some way of knowing what certificates SHOULD be there,
though, there is no way I can imagine that Access - or any program, or
for that matter any human - could ascertain that DWQX33341 is
"missing". I may still be "missing" the point myself though!

John W. Vinson[MVP]

 

[Randy]

I could have several different letters, S, A, Z, X etc. I need a way to
enter the issued certificates in a table, but not individually, I need an
append query that would append the first certificate to the last. I dont
know how that would work with alpanumeric number. Example: Z10000 to
Z10024 verses entering 25 individually. Thanks...Randy

 

[John Vinson]

I could have several different letters, S, A, Z, X etc. I need a way to
enter the issued certificates in a table, but not individually, I need an
append query that would append the first certificate to the last. I dont
know how that would work with alpanumeric number. Example: Z10000 to
Z10024 verses entering 25 individually. Thanks...Randy

By far the simplest approach, then, would be to store this certificate
ID in *two* fields - a one-character Text field for the letter and a
Long Integer field for the number. You can concatenate them for
display and use the missing number code that you already have on the
numeric portion.

John W. Vinson[MVP]