find missing sequence of numbers

[Guest]

I have a table with a column which has a sequence of numbers. Some numbers
are missing. Is there a way for me to find the missing numbers in the
sequence without actually having to eyeball them all?

 

[Rick Brandt]

I have a table with a column which has a sequence of numbers. Some
numbers are missing. Is there a way for me to find the missing
numbers in the sequence without actually having to eyeball them all?

How big is the table? It would be pretty easy to use Excel to create a new
table with a sequence of numbers that has no gaps and then use that to do a
"Find Unmatched Records" query when joined to your existing table.

 

[Bill Edwards]

You could use code like the following:

Public Sub MissingNumbers()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strTable As String
Dim lngNumber As Long
Set db = CurrentDb()
strTable = "SELECT SomeValue FROM tblNumber ORDER BY SomeValue"

Set rst = db.OpenRecordset(strTable)
rst.MoveFirst
lngNumber = rst.Fields("SomeValue")
Do While Not rst.EOF
If rst.Fields("SomeValue") = lngNumber Then
' the number is in sequence
rst.MoveNext
Else
' the number is not in sequence
Debug.Print lngNumber & " is missing"
End If
lngNumber = lngNumber + 1
Loop
db.Close
rst.Close
Set db = Nothing
Set rst = Nothing
End Sub

Instead of writing to the debug window you could write to table or a text
file.

 

[James A. Fortune]

I have a table with a column which has a sequence of numbers. Some numbers
are missing. Is there a way for me to find the missing numbers in the
sequence without actually having to eyeball them all?

Run a Make Table query (sorted by your sequence numbers if they're not
already sorted) to obtain a copy of your table called tblListOfNumbers.
Change the type of the primary key in the copied table to Number/Long
from Autonumber and delete the default value of 0 since it may be
confusing. Delete all the other fields in the copied table. Create a
new autonumber ID field called ID. I am using the name MyList for the
field containing the sequence numbers. Replace MyList wherever it
occurs below to the field name you are using or simply change your field
name to MyList. Your copied table should look something like:

tblListOfNumbers
ID Autonumber
MyList Long
ID MyList
1 1
2 2
3 3
4 5
5 9
6 10
7 22

I want to return:

4 to 4
6 to 8
11 to 21
etc.

qryMissingRange:
SELECT (SELECT A.MyList + 1 FROM tblListOfNumbers AS A WHERE A.ID =
(SELECT A.ID - 1 FROM tblListOfNumbers As A WHERE A.ID =
tblListOfNumbers.ID + 1 AND A.MyList <> tblListOfNumbers.MyList + 1)) &
' to ' & (SELECT A.MyList - 1 FROM tblListOfNumbers AS A WHERE A.ID =
(SELECT A.ID FROM tblListOfNumbers As A WHERE A.ID =
tblListOfNumbers.ID + 1)) AS MissingRange FROM tblListOfNumbers WHERE
(SELECT A.ID - 1 FROM tblListOfNumbers As A WHERE A.ID =
tblListOfNumbers.ID + 1 AND A.MyList <> tblListOfNumbers.MyList + 1) IS
NOT NULL;

(Paste the line above into the SQL area of a new query, close and select
qryMissingRange as the name of the query.)

!qryMissingRange:
MissingRange
4 to 4
6 to 8
11 to 21

(open the query with the design button then click on the red exclamation
point to execute the query.)

The query should give you all the numbers that are missing in the sequence.

The query works by imposing an order on the numbers. Whenever possible
refrain from relying on the order in which records are stored. A
subquery looks for records where the following record does not have a
value that is one higher than the previous record. For the part after
the ' to ' a similar subquery goes to the following record and subtracts
one from that value.

I hope this helps,

James A. Fortune

 

[Guest]

Thank you all.

Run a Make Table query (sorted by your sequence numbers if they're not
already sorted) to obtain a copy of your table called tblListOfNumbers.
Change the type of the primary key in the copied table to Number/Long
from Autonumber and delete the default value of 0 since it may be
confusing. Delete all the other fields in the copied table. Create a
new autonumber ID field called ID. I am using the name MyList for the
field containing the sequence numbers. Replace MyList wherever it
occurs below to the field name you are using or simply change your field
name to MyList. Your copied table should look something like:

tblListOfNumbers
ID Autonumber
MyList Long
ID MyList
1 1
2 2
3 3
4 5
5 9
6 10
7 22

I want to return:

4 to 4
6 to 8
11 to 21
etc.

qryMissingRange:
SELECT (SELECT A.MyList + 1 FROM tblListOfNumbers AS A WHERE A.ID =
(SELECT A.ID - 1 FROM tblListOfNumbers As A WHERE A.ID =
tblListOfNumbers.ID + 1 AND A.MyList <> tblListOfNumbers.MyList + 1)) &
' to ' & (SELECT A.MyList - 1 FROM tblListOfNumbers AS A WHERE A.ID =
(SELECT A.ID FROM tblListOfNumbers As A WHERE A.ID =
tblListOfNumbers.ID + 1)) AS MissingRange FROM tblListOfNumbers WHERE
(SELECT A.ID - 1 FROM tblListOfNumbers As A WHERE A.ID =
tblListOfNumbers.ID + 1 AND A.MyList <> tblListOfNumbers.MyList + 1) IS
NOT NULL;

(Paste the line above into the SQL area of a new query, close and select
qryMissingRange as the name of the query.)

!qryMissingRange:
MissingRange
4 to 4
6 to 8
11 to 21

(open the query with the design button then click on the red exclamation
point to execute the query.)

The query should give you all the numbers that are missing in the sequence.

The query works by imposing an order on the numbers. Whenever possible
refrain from relying on the order in which records are stored. A
subquery looks for records where the following record does not have a
value that is one higher than the previous record. For the part after
the ' to ' a similar subquery goes to the following record and subtracts
one from that value.

I hope this helps,

James A. Fortune