Sequential Numbers

[Brian]

Hello all , I have a table that I want to read that can't be put into an
autonumber so what i want to check is that the numbers are sequential so
if it miss a number I want to know about it.. so for instand 76544 then
76546.. I want 76545 to be put into a table.. any help would be great thanx

 

[John Spencer]

SELECT T1.NumberField -1 as EndOfGap
FROM YourTable as T1 LEFT JOIN YourTable as T2
ON T1.NumberField-1 = T2.NumberField
WHERE T2.NumberField is Null and T1.NumberField > 1

The above should give you the missing numbers in the sequence. Actually it
will give you the highest number in each gap range. So if you had
1,2, 5, 6 , 8 , 9 it should return 4 and 7. It wouldn't tell you that 3
and 4 are missing.

 

[Marshall Barton]

Hello all , I have a table that I want to read that can't be put into an
autonumber so what i want to check is that the numbers are sequential so
if it miss a number I want to know about it.. so for instand 76544 then
76546.. I want 76545 to be put into a table.. any help would be great thanx

This will find the first number of every gap in the
sequence:

SELECT T.num + 1
FROM table As T LEFT JOIN table As X
ON T.num+1 = X.Num
WHERE X.num Is Null

 

[taurus via AccessMonster.com]

This may help. It is a series of queries which you could manage through a
macro. I assume you have a table like tblSequenceMissing holding the initial
data (anyNumber) with missing sequence. Create a blank table
tblSequenceRebuild similar structure with the required field as a primary key.
It will eventually contain the full sequence (newNumber).

qrySequenceAddRebuild:
INSERT INTO tblSequenceRebuild ( newNumber )
SELECT tblSequenceMissing.anyNumber
FROM tblSequenceMissing LEFT JOIN tblSequenceRebuild ON tblSequenceMissing.
anyNumber = tblSequenceRebuild.newNumber
WHERE (((tblSequenceRebuild.newNumber) Is Null))
GROUP BY tblSequenceMissing.anyNumber;

qrySequenceStart:
INSERT INTO tblSequenceMissing ( anyNumber )
SELECT Min([anyNumber])+1 AS start
FROM tblSequenceMissing;

qrySequenceEnd:
INSERT INTO tblSequenceMissing ( anyNumber )
SELECT Max([anyNumber])-1 AS [end]
FROM tblSequenceMissing;

qrySequenceCleanMinimum:
DELETE tblSequenceMissing.anyNumber
FROM tblSequenceMissing
WHERE (((tblSequenceMissing.anyNumber) In (select Min([anyNumber]) AS
deleteNumber from tblSequenceMissing)));

qrySequenceCleanMaximum:
DELETE tblSequenceMissing.anyNumber
FROM tblSequenceMissing
WHERE (((tblSequenceMissing.anyNumber) In (select Max([anyNumber]) AS
deleteNumber from tblSequenceMissing)));

Run the queries in the following loop until no more appends are allowed by
the fourth step : qrySequenceAddRebuild, qrySequenceStart, qrySequenceEnd,
qrySequenceAddRebuild, qrySequenceCleanMinimum, qrySequenceCleanMaximum



Brian wrote:
so for instand 76544 then