Link to home
Start Free TrialLog in
Avatar of Alan Warren
Alan WarrenFlag for Philippines

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
Avatar of flavo
flavo
Flag of Australia image

Only SQL, i dont think so.

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


should be

"INSERT INTO operators ( operatorID, otherfield ) VALUES ( " & getID " & 'myValue' )" (in VB)

or in SQL builder

INSERT INTO operators ( operatorID, otherfield ) VALUES ( getID(), "myValue");
Avatar of will_scarlet7
will_scarlet7

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.
Avatar of rockiroads
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

Avatar of Alan Warren

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
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,
ASKER CERTIFIED SOLUTION
Avatar of arcross
arcross
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
wow, it works. Ive tried myself.

Very very good alan :)
Álvaro,

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
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
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.

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, adOpenForwardOnly, adLockOptimistic
  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(lngNextOperatorNumMax, "00")  '  Format is redundant
 
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
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!
>> 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
>> feb's gonna be cold

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

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.
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
>> 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 }]]]}}}}}{}]))
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!
>> 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.