[AccessD] Checking for "holes" in a sequence using SQL

Arthur,

I was not expecting the database to act intelligently. To be honest I was
not expecting there to even be a solution to this although Gustav figured it
out!

I have an Access database that I used to keep track of the computers at my
company. The computer naming convention that I use is TNCO###. One of the
fields in the table is used for storing the computer name. If I remove a
computer from our network and physical remove it from the company, I would
like to be able to reuse that computer name instead of just using the next
sequential number. To me I do not see it as concatenating two column values
into one but I could be wrong. What do you think Arthur?

JR

-----Original Message-----
From: Arthur Fuller [mailto:artful at rogers.com]
Sent: Wednesday, November 03, 2004 4:44 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Checking for "holes" in a sequence using SQL

Joe Rojas wrote:

>Hi All,
>If I have data similar to:
>JOE001
>JOE002
>JOE003
>JOE005
>JOE006
>
>Is there a way, using just SQL, to return the next available number in the
>sequence, JOE004, and be flexible enough to return JOE007 if JOE004 had
>already existed?
>Or do I have to use code to iterate through the records to check for
>"holes"?
>
>Thanks!
>JR
>
>
>
>
>This electronic transmission is strictly confidential to TNCO, Inc. and
>intended solely for the addressee. It may contain information which is
>covered by legal, professional, or other privileges. If you are not the
>intended addressee, or someone authorized by the intended addressee to
>receive transmissions on behalf of the addressee, you must not retain,
>disclose in any form, copy, or take any action in reliance on this
>transmission. If you have received this transmission in error, please
notify
>the sender as soon as possible and destroy this message. While TNCO, Inc.
>uses virus protection, the recipient should check this email and any
>attachments for the presence of viruses. TNCO, Inc. accepts no liability
for
>any damage caused by any virus transmitted by this email.
> 
>
IMHO something is wrong with this design. You are packing two columns
into one and expecting the database to act intelligently: unfair. If you
really need this column, consider adding two more columns, one with each
fragment and then concatenating the result in the column you have. The
problem becomes much simpler with this approach.

But back to the start. Why are you concatenating two column values into one?

A.
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



This electronic transmission is strictly confidential to TNCO, Inc. and
intended solely for the addressee. It may contain information which is
covered by legal, professional, or other privileges. If you are not the
intended addressee, or someone authorized by the intended addressee to
receive transmissions on behalf of the addressee, you must not retain,
disclose in any form, copy, or take any action in reliance on this
transmission. If you have received this transmission in error, please notify
the sender as soon as possible and destroy this message. While TNCO, Inc.
uses virus protection, the recipient should check this email and any
attachments for the presence of viruses. TNCO, Inc. accepts no liability for
any damage caused by any virus transmitted by this email.