missing sequential numbers?

Question

I have a table that has a number field (not an autonumber). Lets call it [tblRecords].[SequentialNumber].

Is there a way to quickly show me if there are any gaps in the sequentialNumber? In other words, are there any missing records?

ie:

1301

1302

1303

1305

in this example, I would need to know that 1304 is missing.

Thanks in advance.

Answer 1

Assuming that your Table has a design like this

ID -->Autonumber

Field -->e.g Short Text

..lets call it Table1

It should like this

1   A

2   B

3   C

5   D

and you want to know the missing fields

then just make a copy paste of your table...just call it Table2

1   A

2   B

3   C

5   D

no change the ID from AutoNumber to Number

save and close

and again open Table and Add One more Column e.g NewID and set it to AutoNumber

save the new design

Drag both Table1 & Table2 to Query Designer

Copy paste this SQL

SELECT Table2.newID, Table1.ID

FROM Table1 RIGHT JOIN Table2 ON Table1.ID = Table2.newID

WHERE (((Table1.ID) Is Null));

This will show all the orphaned  entries...

Only hiccup is that if the No of Records < last Missing Record then it won't show up.

e.g you have 1000 records and the last Missing Record is 1001 then it won't show up because the AutoNumbering in Table will reach up to 1000.

Answer 2

Indeed. I wrote an article on exactly this subject a decade ago:

Find and Generate Missing Values in an Access Table

Answer 3

You might like to take a look at Subsequences.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes an option which illustrates a query to return Missing Subsequences (Gaps).  The query in question is:

SELECT NZ(EmptySubsequences.StartOfSubsequence,1) AS StartOfSubsequence,

EmptySubsequences.EndOfSubsequence,

EndOfSubsequence-StartOfSubsequence+1 AS LengthOfSubsequence

FROM

    (SELECT DISTINCT

        (SELECT MAX(SeqNumber) +1

         FROM Sequences

         WHERE SeqNumber < Counters.Counter) AS StartOfSubsequence

         (SELECT MIN(SeqNumber) -1

          FROM Sequences

          WHERE SeqNumber > Counters.Counter) AS EndOfSubsequence

     FROM Counters

     WHERE NOT EXISTS

          (SELECT *

           FROM Sequences

           WHERE Sequences.SeqNumber = Counters.Counter)

     AND Counter <         

         (SELECT MAX(SeqNumber)

          FROM Sequences))  AS EmptySubsequences;

Note that the query, in addition to the Sequences table, which includes gaps, also uses an auxiliary Counters table, which is simply a table of sequential numbers with no gaps.  Such a table can easily be populated with a little VBA code, or can be created by sequentially filling down a column in Excel, and then importing the worksheet into Access as table.

Answer 4

Hi Karen, I will try to help.

This needs to be done in Code. You need to loop through the recordset and test that each number is increased by one over the previous. Something like this:

Dim db As Database, rs As REcordset

Dim strSQL As String

Dim prevNumber As Integer

strSQL = SELECT keyfield, numberfield FROM table ORDER BY numberfield;"

SET DB = CurrentDB

SET rs = db.OpenRecordset strSQL

rs.MoveFirst

Do While NOT rs.EOF

  prevNumber = rs.Fields("numberfield")

  rs.MoveNext

  If rs.Fields("numberfield") <> prevNumber+1 Then

       MsgBox prevNumber+1 & " is Missing"

  End If

  rs.MoveNext

Loop