Repeating incrementing numbers

[mcelligott]

I have been asked to create a log in Access to keep track of the tow truck rotation (the order in which the dispatcher should call the tow trucks). The dispatchers call 4 tow trucks and then the rotation starts over again (ex: 451, 452, 453, 454, 451, 452, 453, 454, etc).

My first issue is getting the tow unit # to increment like the above example.

My second issue is if one of the units gets cancelled after being dispatched, it needs come up again as the next unit in the rotation again.

Any suggestions?

 

[r937]

how often would these dispatches occur? certainly not as frequently as would cause any concern about multi-threading concurrent users

there are only 4 trucks, after all

so create a Next table, give it an integer column called TruckNo, and hardcode the logic so that when you need to dispatch a truck, you SELECT TruckNo, and then immediately UPDATE it to the next value

update Next 
   set TruckNo 
     = iif(TruckNo=454,451,TruckNo+1)

if the dispatch is cancelled, you simply put the number back into the table as the next one to go

update Next 
   set TruckNo 
     = 453

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)