Find a missing number in a seqeunce

  • Thread starter Thread starter Topher
  • Start date Start date
T

Topher

I need to insert a new record in a table. Each record has a column with
a value between 0 and 255. When I insert the new record, it needs to
have a number that does not match any of the numbers in and existing
records. In other words, I need to find the first number that does not
already exist in the table. If the records had numbers 1,2,4,5,6 and 7,
then the new record would need to have the number 3 in this field. How
can I find the first number that does not exist?
 
Try this --
SELECT TOP 1 [EW-LWC_1].[NUM]+1 AS Expr1
FROM [EW-LWC], [EW-LWC] AS [EW-LWC_1]
WHERE ((([EW-LWC].NUM)<>[EW-LWC_1].[NUM]))
GROUP BY [EW-LWC_1].[NUM]+1
ORDER BY [EW-LWC_1].[NUM]+1;

EW-LWC is the table and EW-LWC_1 is the same table with suffix of _1 added.
NUM is the field with your numbers.
 
Alternately you could run through the table row by row until you find a
skipped number and return that in vba:

Public Function LowNumber() As Integer
Dim rst As Recordset ' Your Table
LowNumber = 0

' Select your table and sort is in ascending order (lowest to highest)
Set rst = CurrentDb.OpenRecordset("SELECT FIELDNAME FROM TABLENAME ORDER BY
FIELDNAME;")
' Repeat until the difference between the number in one row and the next
is > 1
Do Until rst("FIELDNAME") > LowNumber + 1
' If this is the first row and it's not a 1, define as 1 and exit the
sub
If LowNumber = 0 And rst("FIELDNAME") <> 1 Then LowNumber = 1: Exit Sub
' Set the lownumber to the current row number
LowNumber = rst("FIELDNAME")
rst.MoveNext ' Move to the next record
Loop
' LowNumber is currently the lowest row with a populated number before a
skipped number
' Add one to make it the lowest skipped number
LowNumber = LowNumber + 1
rst.Close ' Close the recordset
End Function

Hope this helps.

Theo Geer
 
With that range of only 256 numbers, I would just add a simple number table
with one field with the numbers 0 to 256 in it.

Then to find out the lowest available number you could use a query to get
the minimum number

SELECT Min(NumberTable.Number)
FROM NumberTable LEFT JOIN YourCurrentTable
ON NumberTable.Number = YourCurrentTable.YourNumberField
WHERE YourCurrentTable.YourNumberField Is Null

If this returns 256 then you have run out of numbers
 
Back
Top