record gaps 1

[scasystems]

Got a table with primary key ranging from 1 to 3000
Need to find unused numbers between 1 to 3000.
How?

 

[mndrlion]

Step 1: Create a table called tblAllNumbers with a field called theNumber make the length long.

Step 2: Write a procedure that will populate tblAllNumbers with the dsired values. This is a simple loop.

Sub FillAllNumbers()

    Dim strSQL as String
    Dim lngNumber as Long

    For lngNumber = 1 to 3000
       strSQL = "INSERT INTO tblAllNumbers (theNumber) Values (" & lngNumber & ");"
       currentdb.execute strSQL
    next lngNumber
End Sub

Step 3: Create a query with an outer join

SELECT tblAllNumbers.theNumber, tblYourTable.PK
FROM tblAllNumbers LEFT JOIN tblYourTable ON tblAllNumbers.theNumber = tblYourTable.PK
WHERE (((tblYourTable.PK) Is Null));