identify missing numbers in sequence of records

  • Thread starter Thread starter Tom Whyte
  • Start date Start date
T

Tom Whyte

Please can someone tell me how to identify gaps in supposedly sequential data
and calculate the value of teh difference. Thanks.
 
Rank the data, then subtract the value from the rank. GROUP on that
difference, finding the MIN (start of un-interrupted sequence) and MAX (end
of un-interrupted sequence) for each of these groups:


SELECT MIN(valueField) As start, MAX(valueField) AS ending
FROM somequery
GROUP BY valueField-Rank



To get the rank, many possibilities. Here is one (which will be the
'somequery' you will use here up) :

SELECT a.valueField, COUNT(*) AS rank
FROM table AS a INNER JOIN table AS b
ON a.valueField >=b.valueField
GROUP BY a.valueField



As example, with:

Table
valueField
1
2
3
5
6
7
8
9
11
12



The somequery (the last one) return:


valueField rank
1 1
2 2
3 3
5 4
6 5
7 6
8 7
9 8
11 9
12 10


And the first query will compute valueField-rank, and group on these
differences:

valueField rank
1 1 0
2 2 0
3 3 0
5 4 1
6 5 1
7 6 1
8 7 1
9 8 1
11 9 2
12 10 2



returning
start ending
1 3
5 9
11 12


since the un-interrupted sequences are from 1 to 3, 5 to 9, and 11 to 12



Vanderghast, Access MVP
 
I got this from here before and it works. It is slow if you have many
thousands of records.
Table tblListOfNumbers has field ID as autonumber and MyList as integer
field with the number sequences to be checked.

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;
 
The list needs to be in ascending along with the ID as below --
ID MyList
1 1
2 2
3 3
4 6
5 7
6 8
7 9
8 11

It will result in '4 to 5' and '10 to 10'
 
Back
Top