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

Joe
The only way I can come up with is if you created a table of all possible
numbers, left joined that to this table, had a WHERE statement that limited
the selection to records where the key on the joined table (yours) was null
(ie no match) and returned the TOP 1 of those.

-- Andy Lacey
http://www.minstersystems.co.uk 

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe Rojas
> Sent: 03 November 2004 21:02
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Checking for "holes" in a sequence using SQL
> 
> 
> 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.
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
>