Missing sequential numbers

[leebo1973]

hi all

I have a list of what should be sequentaia numbers, but I am finding instances were a number seems to be missing

Is there a way to query the whole dataset and find all missing instances without having to trawl he list?

Thanks
Lee

 

[plog]

Well, queries can't create data, so you will need a table of all the numbers that should be in your data. Let's call the comprehensive list COMP and your table to check TableA and for good measure F is the name of the relevant field in both tables.

Create a query using COMP and TableA, join them on their F fields and then change the join to a LEFT JOIN showing all from COMP. Bring down F from both tables and underneath TableA.F put in the criteria "Null". Run that and it will show all missing values.

 

[pr2-eugin]

Well this could work,

Public Sub findMissingID()
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
    Dim rsID As DAO.Recordset, conID As Long
    
    Set rsID = CurrentDb.OpenRecordset("[COLOR=Blue]yourTableName[/COLOR]")
    
    Do While Not rsID.EOF
        conID = conID + 1
        rsID.FindFirst "[COLOR=Blue]yourIDFieldName [/COLOR]= " & conID
        If rsID.NoMatch Then Debug.Print conID
        rsID.MoveNext
    Loop
    
    Set rsID = Nothing
End Sub

The code will loop through yourTableName, and check with the corresponding ID with a continuous incitement value. If it cannot find it, it will display the number. Run the code from the immediate window (CTRL+G from the VBE). The result will be printed.

Just that you know, this code might struggle if the Dataset is huge.

 

[CJ_London]

Or a bit of sql that should do the job:

Select seqno+1 from mytable where seqno+1<>(select min(seqno) from mytable as tmp where seqno>mytable.seqno)

 

[Mile-O]

I would have thought the first question to ask was whether this list of sequential numbers was based on an Autonumber field. If so, gaps are to be expected, and meaning shouldn't really be ascribed to them.