Alan Warren
asked on
Select first unused number in a range between 00 and 99
Hi
I have a table called Operators, for some obscure reason that defies logic, a numeric field called OperatorID of type Long Integer has been throttled by a business policy, stating that "there shall never be more than 100 operators" hmmmm.
The throttling is in the form of an input mask '00' and a format of '00' being applied at table level indexed(no duplicates).
The Problem is this:
I need to assign a new OperatorID, now operatorID's 01, 02 and 03 have been assigned, as have 99, 98, 97.
I need some SQL script that will return the first available number in the range 1 - 100 that has not been consumed.
Hope that is clear enough for you, if not, let me know, and I will attempt to clarify.
Dont really want to use a function(builtin or custom) for this if at all possible, want it in SQL script please.
Regards Alan
I have a table called Operators, for some obscure reason that defies logic, a numeric field called OperatorID of type Long Integer has been throttled by a business policy, stating that "there shall never be more than 100 operators" hmmmm.
The throttling is in the form of an input mask '00' and a format of '00' being applied at table level indexed(no duplicates).
The Problem is this:
I need to assign a new OperatorID, now operatorID's 01, 02 and 03 have been assigned, as have 99, 98, 97.
I need some SQL script that will return the first available number in the range 1 - 100 that has not been consumed.
Hope that is clear enough for you, if not, let me know, and I will attempt to clarify.
Dont really want to use a function(builtin or custom) for this if at all possible, want it in SQL script please.
Regards Alan
should be
"INSERT INTO operators ( operatorID, otherfield ) VALUES ( " & getID " & 'myValue' )" (in VB)
or in SQL builder
INSERT INTO operators ( operatorID, otherfield ) VALUES ( getID(), "myValue");
"INSERT INTO operators ( operatorID, otherfield ) VALUES ( " & getID " & 'myValue' )" (in VB)
or in SQL builder
INSERT INTO operators ( operatorID, otherfield ) VALUES ( getID(), "myValue");
Do you have your range in a table? This may sound clunky but I would use a table that contains the whole range of operator IDs as well as the operator name, then you can easily build an SQL that finds the first unused ID.
I was thinking along the lines of having a table holding values 1 to 100
then u can do something like this
select top 1 num from lookupnumberstable where num not in (select operatorid from operators) order by num
then u can do something like this
select top 1 num from lookupnumberstable where num not in (select operatorid from operators) order by num
ASKER
Hi Dave :)
how ya going mate?
This has to be do-able, without temp tables and without a vba function.
never say never.
Using a temp table that contains the range we could:
Select Top 1 NumField from tblRange where Numfield Not In(Select OperatorID From Operators) From tblRange
Was hoping not to create temp table, MDE.
Alan
how ya going mate?
This has to be do-able, without temp tables and without a vba function.
never say never.
Using a temp table that contains the range we could:
Select Top 1 NumField from tblRange where Numfield Not In(Select OperatorID From Operators) From tblRange
Was hoping not to create temp table, MDE.
Alan
damn, too slow, all these posts been added, should of refreshed!
anyway looks like Will has the same idea as me but has beat me to it
Morning Dave,
anyway looks like Will has the same idea as me but has beat me to it
Morning Dave,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Think I got it guys!
SELECT TOP 1 [OperatorID]+1 AS NextID FROM Operators
WHERE ((([OperatorID]+1) Not In (select OperatorID From Operators) And ([OperatorID]+1)<100))
ORDER BY [OperatorID]+1;
Thanks anyway.
SELECT TOP 1 [OperatorID]+1 AS NextID FROM Operators
WHERE ((([OperatorID]+1) Not In (select OperatorID From Operators) And ([OperatorID]+1)<100))
ORDER BY [OperatorID]+1;
Thanks anyway.
wow, it works. Ive tried myself.
Very very good alan :)
Very very good alan :)
ASKER
Álvaro,
I read your post after I posted my "I got it guys" post.
Exactly what I did, good one friend, thank you.
Alan
I read your post after I posted my "I got it guys" post.
Exactly what I did, good one friend, thank you.
Alan
hey thanks for the points Alan :)
but you did it !!
again, very good !
Álvaro
but you did it !!
again, very good !
Álvaro
ASKER
Your logic was good Álvaro.
Instead of selecting what was there, select what wasn't there; by adding one and filtering unmatched on what was there.
good one
Instead of selecting what was there, select what wasn't there; by adding one and filtering unmatched on what was there.
good one
Alan,
Long time no see mate. How's things going down in Adelaide. You been busy with work??
Rocki,
Ill be going for an adventure to the UK for about 6 months {i here you Europeans have transport problems too} next February, ill have to meet up with you somewhere and buy you that Kronie.
Keep up the good work guys, i guess my "love" of VB gets the better of me...
Dave.
Long time no see mate. How's things going down in Adelaide. You been busy with work??
Rocki,
Ill be going for an adventure to the UK for about 6 months {i here you Europeans have transport problems too} next February, ill have to meet up with you somewhere and buy you that Kronie.
Keep up the good work guys, i guess my "love" of VB gets the better of me...
Dave.
ASKER
Hi Dave,
things is pretty good here, eeking out a megre existance contracting, sick of this long winter.
Hows them traffic control algorithm's going, can you arrange me a clear run through Brisbane in late September?
Got a strong affinity with VBA too, but I always prefer to solve it with SQL if possible, its easilly preserved as serverside procedure. To be honest mate, it ended up being an ADO/SQL/VBA function anyway.
Function mGetNextOperatorID() As Long
' aw 6-aug-2004
'=========================
' Function to generate new OperatorID
'=========================
Dim lngNextOperatorNumMax As Long
Dim sql As String
Dim sWhere As String
Dim sOrderBy As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
sql = "SELECT TOP 1 [OperatorID]+1 AS NextID FROM Operators"
sWhere = " WHERE ((([OperatorID]+1) Not In (SELECT OperatorID FROM Operators) And ([OperatorID]+1)<100))"
sOrderBy = " ORDER BY [OperatorID]+1"
sql = sql & sWhere & sOrderBy
rs.Open sql, CurrentProject.Connection,
With rs
If Not .BOF And Not .EOF Then
lngNextOperatorNumMax = .Fields("NextID")
Else
lngNextOperatorNumMax = 0
End If
.Close
End With
Set rs = Nothing
mGetNextOperatorID = Format(lngNextOperatorNumM
End Function
Take care mate, stay in touch, maybe catch you when Im in town.
Alan
I only just realised that it was you how asked the Q. I would have said hello earlier.
I'm still stick with A97 at work, hence the DAO, plus i wanted to get my train go home, and didnt really want to play with SQL.
Clean run in Brissie, good luck, unless you are driving at 2am and you're lucky.
Take care buddy,
Dave
I'm still stick with A97 at work, hence the DAO, plus i wanted to get my train go home, and didnt really want to play with SQL.
Clean run in Brissie, good luck, unless you are driving at 2am and you're lucky.
Take care buddy,
Dave
Dave, transport problems magnified in the UK, especially these damn trains! Im fed up with commuting!
feb's gonna be cold, good idea to meet up,
been damn hot last two days, yesterday I spent all day lazing on the grass, at the cricket ground with bbq and loads of beer, no krony but stella was there. I was totally shagged by the end of the day, heat and beer!
feb's gonna be cold, good idea to meet up,
been damn hot last two days, yesterday I spent all day lazing on the grass, at the cricket ground with bbq and loads of beer, no krony but stella was there. I was totally shagged by the end of the day, heat and beer!
>> transport problems magnified in the UK
Glad to hear (for my sake)
>> feb's gonna be cold
Yeah i know, and it sickens me.
>> at the cricket ground with bbq and loads of beer
You an Aussie mate, that's my perfect summer Sunday.
>> heat and beer
It kills everyone, but they go thogether soooo well.
My folks are moving over there soon (just sold their house over here) so im going to bum with them for a while, cheap accom, cant complian with that.
Catch you both later.
Dave
Glad to hear (for my sake)
>> feb's gonna be cold
Yeah i know, and it sickens me.
>> at the cricket ground with bbq and loads of beer
You an Aussie mate, that's my perfect summer Sunday.
>> heat and beer
It kills everyone, but they go thogether soooo well.
My folks are moving over there soon (just sold their house over here) so im going to bum with them for a while, cheap accom, cant complian with that.
Catch you both later.
Dave
>> feb's gonna be cold
and august too. I havent seen a bloody sunny day this summer yet.
and august too. I havent seen a bloody sunny day this summer yet.
It sounds like its cold 24/7/365. Does it get over 30C?? I used to live in Townsvile (north Qld - Australia) and 35 was "cool" for summer. 30C wasn't unusual for "winter".
its been damn hot last weekend, temp about 26 to 28C
now its just chucking it down, so much rain, but still warm
now its just chucking it down, so much rain, but still warm
Dave, sorry for dissapoint you, but 30C!!! you are joking !! I've been over 2 years here and maybe its gone over that, once or twice. I used to live in Alicante (Spain) and i think they are having 35 average.
Now, im getting used to it.
Now, im getting used to it.
Well, true, last weekend was good maybe best of the year, nearly 30C!
Dave, warmest months August-September (mid)
Its hot enough to merit putting on the AC in the car, otherwise your messed!
Occasionally July and June, even May
Decent weather in April, though it has snowed once, that was a freak
starts getting cold October, gets better after feb
from my experience, south of England is generally a little warmer than the north. But then maybe it depends on whereabouts you are.
Bring your winter woolies, not the aussie ones, t-shirt and shorts but the british ones
Its hot enough to merit putting on the AC in the car, otherwise your messed!
Occasionally July and June, even May
Decent weather in April, though it has snowed once, that was a freak
starts getting cold October, gets better after feb
from my experience, south of England is generally a little warmer than the north. But then maybe it depends on whereabouts you are.
Bring your winter woolies, not the aussie ones, t-shirt and shorts but the british ones
>> 26 to 28C
Hot?? my @$%#%@!!!!
That's cold dude!!!
Its winter and gets up to 25+ most days during the day ( below 5 is really cold at night, and thats cold......brrrrrr.... - considering no-one has heating or anytihng, only the "rich" have air-con, and thats only used during summer, even if it is reverse cycle {i havent used mine at all since Feb - summer} ) (({{}{{{{{{[[[{ cheese }]]]}}}}}{}]))
Hot?? my @$%#%@!!!!
That's cold dude!!!
Its winter and gets up to 25+ most days during the day ( below 5 is really cold at night, and thats cold......brrrrrr.... - considering no-one has heating or anytihng, only the "rich" have air-con, and thats only used during summer, even if it is reverse cycle {i havent used mine at all since Feb - summer} ) (({{}{{{{{{[[[{ cheese }]]]}}}}}{}]))
thats why the assuie cricket team only come in the english summer, cant stand the cold
now play the english in cold conditions, perhaps the english may win the ashes for once!
now play the english in cold conditions, perhaps the english may win the ashes for once!
>> for once!
...... good luck, they'll need more than that!
Im off to bed, tooo many beers ( public holiday tomorrow, its after 11pm ). Had a few too many brews at a mates house after work.
Catch you all later.
...... good luck, they'll need more than that!
Im off to bed, tooo many beers ( public holiday tomorrow, its after 11pm ). Had a few too many brews at a mates house after work.
Catch you all later.
Public function getID() as integer
Dim db as dao.database
Dim rs as dao.recordset
Dim lLast as long
Set db = currentdb
Set rs = db.openrecordset("Select OperatorID from Operators")
rs.movefirst
lLast = rs(0).Value
rs.movenext
While not rs.EOF
If rs(0).value - 1 <> lLast then
getID = lLast + 1
End if
lLasr = rs(0).value
rs.movenext
Wend
End function
You can use it in SQL like
INSERT INTO operators ( operatorID, otherfield ) VALUES " & getID " & 'myValue'"
Dave