Finding numbers not in sequence

[David O]

How can I find the missing numbers in an incremental sequence of numbers? I
have a field that contains numbers fom 1 to about 6000. When records are
deleted a gap in the numbering occurs. I want to find and list all the
missing numbers.
Thanks
David O

 

[Ken Snell MVP]

Build a table with a single field in it; let's call the table tblNumbers,
and the field NumValues.

Open the table in datasheet view.

In an EXCEL file, create a column of numbers, starting at 1 and going to
10000 or so. Copy that column, and paste it into the table.

Now use a query to get the missing numbers:

SELECT NumValues
FROM tblNumbers
LEFT JOIN YourTableName
ON tblNumbers.NumValues =
YourTableName.NumberFieldName
WHERE YourTableName.NumberFieldName IS NULL;

 

[David O]

I read through your solution and will give it a try. I understand it's logic
so I am sure it will work.

 

[John Spencer]

You might try the following and see if it works for you:
Assumptions:
== First Number is 1 and is present
== All numbers are positive. (this may or may not return correct results for
negative numbers if you drop the A.SomeNumber condition.

SELECT A.SomeNumber-1
FROM YOurTable as A LEFT JOIN YourTable As B
ON A.SomeNumber = B.SomeNumber+1
WHERE B.SomeNumber is Null
AND A.SomeNumber > 1

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County