Find missing number in a sequence through query

Do you know what the start and end numbers are of the sequences?  Will the gaps be large?

To start with, create a tally table, this is a table with a bunch of numbers in it.  I generally use tbl_Numbers with one field (lngNumber) and ten records (0-9).  I can then use that table to create a query (qry_Number) to generate any range of numbers, I generally have two of these in my databases, one for the numbers 0-99 and another for 0-999.  The query for the numbers to 99 looks like:

SELECT Tens.lngNumber * 10 + Ones.lngNumber from tbl_Numbers as Tens, tbl_Numbers as Ones

Select allOpen in new window

Start out with a query that looks something like:

SELECT T1.SeqID, Min(T2.[SeqID]) AS NextValue
FROM tbl_Seq as T1
INNER JOIN tbl_Seq AS T2 ON T2.SeqID > T1.SeqID
GROUP BY T1.SeqID
HAVING MIn(T2.SeqID) <> T1.SeqID + 1

Select allOpen in new window

This query uses a non-equi join to join the table to itself and identify the minimum value of the SeqID field greater than the current value, and then only selects the records where there is a gap of greater than 1.  This first query will also allow you to assess the magnitude of the gaps and determine whether you can use qry_Numbers_0_to_99 or need to use qry_Numbers_0_to_999.

Then you would wrap this as a subquery and join it to the appropriate qry_Numbers using a Cartesian join to return the list of missing values.

SELECT [SeqID]+[lngNumber] AS MissingValues
FROM (
SELECT T1.SeqID, Min(T2.[SeqID]) AS NextValue
FROM tbl_Seq as T1
INNER JOIN tbl_Seq AS T2 ON T2.SeqID > T1.SeqID
GROUP BY T1.SeqID
HAVING MIn(T2.SeqID) <> T1.SeqID + 1
)  AS T3, qry_Numbers
WHERE ([SeqID]+[lngNumber]<[NextValue]) 
AND (qry_Numbers.lngNumber<>0)

Select allOpen in new window

I once published an article on exactly this:

Find and Generate Missing Values in an Access Table

It uses a collection to hold the found missing numbers:

Private Sub btnSearch_Click()

' Read table/query sequentially to 
' record all missing IDs.
' Fill a ListBox with missing values.
' A reference to Microsoft DAO must be 
' present.

  ' Define search table or query.
  Const cstrTable As String = "Orders"
  Const cstrField As String = "OrderID"
  
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim lst As ListBox
  Dim col As Collection
  
  Dim strSQL As String
  Dim strList As String
  Dim lngLast As Long
  Dim lngNext As Long
  Dim lngMiss As Long
    
  strSQL = "Select " & cstrField & "" _
   & " From " & cstrTable & " _
   & " Order By 1;"  
  
  Set lst = Me!lstMissing
  Set col = New Collection
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strSQL)
  
  If rst.RecordCount = 0 Then
    'The recordset is empty.
    'Nothing to do.
  Else
    lngLast = rst(cstrField).Value
    rst.MoveNext
    While rst.EOF = False
      lngNext = rst(cstrField).Value
      For lngMiss = lngLast + 1 To _
       lngNext - 1
        col.Add (lngMiss)
      Next
      lngLast = lngNext
      rst.MoveNext
    Wend
      'Generate next value in sequence.
      'Discard if collecting only 
      'missing values.
      col.Add (lngLast + 1)
  End If
  rst.Close
  
  'Populate list box from collection.
  For lngMiss = 1 To col.Count
    If Len(strList) > 0 Then 
      strList = strList & ";"
    End If
    strList = strList & col(lngMiss)
    Debug.Print col(lngMiss)
  Next
  lst.RowSource = strList
  Debug.Print strList
  
  Set rst = Nothing
  Set dbs = Nothing
  Set col = Nothing
  Set lst = Nothing
  
End Sub

Select allOpen in new window

A demo is attached the article.

@Gustav Brock. Thanks for your help. can we do it with a continuous form rather than listbox?

My query would return the numbers in a recordset, which could be used in the continuous form.

can we do it with a continuous form rather than listbox?

Yes, add the identified numbers to a table instead of adding them to the collection.
This way you could also add a field to mark/unmark the found numbers.

@Dale Fye  Thanks for your help. Can you please attach a demo for 3rd query. 2nd query works well.

not sure what you mean, 3rd query.

SELECT [SeqID]+[lngNumber] AS MissingValues
FROM (
SELECT T1.SeqID, Min(T2.[SeqID]) AS NextValue
FROM tbl_Seq as T1
INNER JOIN tbl_Seq AS T2 ON T2.SeqID > T1.SeqID
GROUP BY T1.SeqID
HAVING MIn(T2.SeqID) <> T1.SeqID + 1
)  AS T3, qry_Numbers
WHERE ([SeqID]+[lngNumber]<[NextValue])
AND (qry_Numbers.lngNumber<>0)

This query give error. Please give me a demo.

It should ne noted that Gustav's procedure finds missing numbers between the first number in the list and the last number in the list, not between a defined range.  That means if there are number missing at the very beginning or end, they won't be included in the list.

Well, neither would mine for that matter.  

I can understand maybe needing to know numbers which are missing before the minimum value in a field, but how do you find missing numbers at the end of a list?

If the highest number in your list is 2388 and your upper limit is 2390, then you are missing every number between 2388 and 2390, which is why you need a way to tell the process to look for missing numbers all the way to the upper limit, not just the last number in the list.  Remember, the author wants the solution to spit-out EVERY missing ID number between a range (and I am assuming that he wants the limits included).

I'm waiting to see who comes up with a solutions that is "complete" as far as what the author needs as stated in his initial post.  Too many "solutions" are incomplete as they don't give the author the exact results he is looking for.

...one of my pet peeves when it comes to these Q&A forums.....

Again, this is assuming that the author does NOT have a "master ID list" to compare against the list with missing IDs.  If he had a master list, then the unmatched queries first proposed would work.

actually, Mark, the OP says "missing number in a sequence by applying query", not numbers in a "range".

My requirememt is fulfilled. Thanks to Dale Fye.

Sorry Dale.  I could have sworn the question had an initial range of 2600 to 3000, but that could have been another, similar question in another thread.  I didn't recognize the author's initial question as the one I thought I was answering.... duh....

Sorry for the confusion....

happens to  me all the time, Mark.  <g>

That's why I had to go back and look after your comment.