Record Numbers

[SteveBexley]

Hi
Can anybody suggest a way I can add numbers / records to a membership table based on a membership number so that I can ralocate a number when a member drops out of the list.
eg: record numners 1,2,3,5,6,7 record number 4 has been deleted and so I am trying to create a function that gives me the next number to allocate or a missing record number in this case number 4. so I can give the next member the number 4 when I create anew record.
Hope you guys can help Im not very experienced but learning quickly.
Steve

 

[Golom]

The Following SQL will retrieve the first missing number in a list of numbers or the next number in sequence if there are no missing numbers.

SELECT TOP 1 A.Num+1 AS Missing
FROM  tbl AS A
WHERE  NOT EXISTS (Select * From tbl As X Where X.Num = A.Num + 1)
ORDER BY A.Num;