Sequential Numbering 1

[dar149]

I have a database table that has numbers in sequential order. There are 99,000 numbers. However, there have been deleted records, so there are around 8000 numbers that aren't being used. Is there a way to get a list of all unused numbers?

Thanks...

 

[Golom]

First, create a table of integers (named "Integers") like this
[tt]
Int
0
1
2
3
:
8
9
[/tt]
Then build a query (call it Integers10000)

SELECT 10000*tenthousands.Int + 1000*thousands.Int + 100*hundreds.Int + 10*tens.Int + units.Int AS iiii
FROM integers AS tenthousands, integers AS thousands, integers AS hundreds, integers AS tens, integers AS units

Then Join that to your table that you want to detect missing values in.

SELECT I.iiii

FROM myTable AS B RIGHT JOIN Integers10000 AS I ON B.NumberValue = I.iiii

WHERE B.NumberValue IS NULL AND  I.iiii <=(Select MAX(NumberValue ) From myTable)

ORDER BY I.iiii

 

[Ricky1946]

What about creating a query with 'IsNull' in the criteria search box for any field that is NOT populated.


"To say 'thankyou' encourages others."

 

[GingerR]

i wrote a bit of code to alleviate you from making a big table:

1) make a new table called TempTable. Put in one field, called "UnusedNumber", field size = LONG INTEGER.

2) make a new module. you don't even have to save it. paste in this codeTweak as necessary:
a) substitute the name of your existing table for OrigTable
b) sub the name of your existing ID field for [ID]
c) sub 10000 for 20 (for i = 1 to 10000)
d) make sure reference to DAO is made

Function GetUnusedNumbers()
    Dim rs, rsnew As DAO.Recordset
    Dim i As Long
    Set rsnew = CurrentDb.OpenRecordset("TempTable")
    For i = 1 To 20
        Set rs = CurrentDb.OpenRecordset("Select * from OrigTable where [ID] = " & i)
        If rs.RecordCount = 0 Then
            rsnew.AddNew
            rsnew!UnusedNumber = i
            rsnew.Update
        End If
        Set rs = Nothing
    Next i
End Function

then simply run the function and it will make your new table with your "unused" numbers.