Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select first unused number in a range between 00 and 99

Posted on 2004-08-06
26
Medium Priority
?
2,249 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:Alan Warren
  • 8
  • 6
  • 6
  • +2
26 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 11733608
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


0
 
LVL 34

Expert Comment

by:flavo
ID: 11733612
should be

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

or in SQL builder

INSERT INTO operators ( operatorID, otherfield ) VALUES ( getID(), "myValue");
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11733615
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.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 65

Expert Comment

by:rockiroads
ID: 11733631
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

0
 
LVL 26

Author Comment

by:Alan Warren
ID: 11733633
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11733637
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,
0
 
LVL 8

Accepted Solution

by:
arcross earned 2000 total points
ID: 11733681
why not to create a query in your table Operators adding a counter agaisnt

counter    OperatorID
-------------------------
1                 1
2                 2
3                 4 -------->> Here you wouls see the diff
4                 5
.....................

Then select top as rocki mentioned. All this you could do in the same query or using 2 queries.

Álvaro
0
 
LVL 26

Author Comment

by:Alan Warren
ID: 11733693
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.
0
 
LVL 8

Expert Comment

by:arcross
ID: 11733741
wow, it works. Ive tried myself.

Very very good alan :)
0
 
LVL 26

Author Comment

by:Alan Warren
ID: 11733743
Álvaro,

I read your post after I posted my "I got it guys" post.
Exactly what I did, good one friend, thank you.

Alan
0
 
LVL 8

Expert Comment

by:arcross
ID: 11733758
hey thanks for the points Alan :)

but you did it !!

again, very good !

Álvaro
0
 
LVL 26

Author Comment

by:Alan Warren
ID: 11733773
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
0
 
LVL 34

Expert Comment

by:flavo
ID: 11741038
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.
0
 
LVL 26

Author Comment

by:Alan Warren
ID: 11741073

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


0
 
LVL 34

Expert Comment

by:flavo
ID: 11741097
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11750737
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!
0
 
LVL 34

Expert Comment

by:flavo
ID: 11750816
>> 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
0
 
LVL 8

Expert Comment

by:arcross
ID: 11761889
>> feb's gonna be cold

and august too. I havent seen a bloody sunny day this summer yet.  
0
 
LVL 34

Expert Comment

by:flavo
ID: 11761934
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".
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11761958
its been damn hot last weekend, temp about 26 to 28C
now its just chucking it down, so much rain, but still warm

0
 
LVL 8

Expert Comment

by:arcross
ID: 11761987
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.
0
 
LVL 8

Expert Comment

by:arcross
ID: 11761994
Well, true, last weekend was good maybe best of the year, nearly 30C!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11762046
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
0
 
LVL 34

Expert Comment

by:flavo
ID: 11762064
>> 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 }]]]}}}}}{}]))
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11762151
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!
0
 
LVL 34

Expert Comment

by:flavo
ID: 11762213
>> 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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question