Determining Sequential Numbers (1 Viewer)

[Topher]

Is it possible to write a query that can determine if a list of records are in sequential order?

ie 1,2,3,4

what i want to do is find out of say 1000 records if there is are some free sequential #s. the sequential #s are then given to some other records for processing. also the user selects how many sequential records they need - and they can also put in the # they want to start at - or just let the system find the sequence. i have to be able to look for the sequence because the user can also reserve #s they want or need. so that could leave #s in the middle that are not used yet.

any help appreciated! =D

 

[llkhoutx]

Use DAO code to cycle through the records, saving the Seq #s skipped in a temporary table.

 

[Topher]

but wouldn't using code be extremely slow? theres probably no other way than using code i suppose.

thanks

 

[simongallop]

Another way is to create a table that contains the number range and then use a query showing all the numbers that are not held in the main table. You could expand this so that code creates the data between the requested numbers. Ought to be a bit quicker as the querydef will be compiled and looping through a table to add numbers is very quick.

HTH

 

[Topher]

hmmm sounds interesting i'll give that idea a go..

cheers,

T