Finding numbers not in sequence

  • Thread starter Thread starter David O
  • Start date Start date
D

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
 
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;
 
I read through your solution and will give it a try. I understand it's logic
so I am sure it will work.
 
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
 
Back
Top