missing sequential numbers query

[selyob]

I have a table containing serialized customer numbers that have to be
in the range of 1-9999. It has to be 1-9999 because it has to be
exported to an older COBOL database which only has 4 characters in the
field. I cannot assign a number twice to the same customer so the
customerid field index is set to no duplicates. When I have a new
customer I need a query to show all available serial numbers to display
in a combo box list. For example: below customer number 0003 is
available. In addition, if I delete a customer then I need to know this
customerid is available for the next person. I know it would work if I
just populated a table with one field with numbers 1-9999 and just
created a mismatch query but there should be a way to perform this
programatically. If lets say Visa did this with credit card numbers
they would have to have a table with millions of records containing
numbers that are available, so making a huge table just for record
numbers does not seem like the correct way to perform this. I also
have this running in a multi-user environment so I am trying to make it
avoid assigning the same number to two people.


0001 John Q Customer
0002 John Doe
0003
0004 Jane Doe

 

[P Mertens]

Hi,

I solved this problem once using the other table containing all numbers.
Then the "not matched query", and take the first available ...
To avoid assigning more than once ... you could flag the assigned number in
the "all numbers table" and have your "not matched query" filter on this ..
be sure to reset the flag when the user leaves your transaction without
saving ... or run a reset query every night to unassign the locked numbers


0001 John Q Customer 0001 Assigned = true
0002 John Doe 0002 true
0004 Jane Doe 0003 true
0004 true
0005 false

0003 is currently assigned, but not yet saved ...
Next number is now 0005, if 0003 not saved becomes available again

Regards,
Patrick

 

[John Nurick]

A table containing 10,000 4-digit numbers is a *long* way from being
huge, and IMHO a query is the simplest and fastest, e.g.

SELECT MIN(AllNumbers.TheNumber) AS FirstAvailableNumber
FROM AllNumbers LEFT JOIN Customers
ON Numbers.TheNumber = Customers.ID
WHERE Customers.ID IS NULL
;

Make sure that both fields are indexed.

The alternative seems to be to do something like this

rs = db.OpenRecordset("SELECT ID FROM Customers ORDER BY ID")
LastValue = 0
Do Until rs.EOF
If rs.Fields(1).Value > LastValue Then
'LastValue is available
Exit Do
Else
LastValue = LastValue + 1
rs.MoveNext
End If
Loop
If rs.EOF Then
'No values available!!!
End If

which is a great deal more work.