Record Routing using MoveFirst and MoveNext

[Guest]

I have been using the code below to distribute records in a round robin
fashion to 3 different lists in access. I now need to add a fourth list but
cannot get the code to work. 'Queue' or 'Q' refers to the list that each
record will be routed to. Can anyone help?

Private Function SetQueueRouting()
'-- Added 12/12/2004
'--Sets queue_key field to the queue that was not routed to the last two deals
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim temp As Integer
Dim MyQ As Integer

Set db = CurrentDb
'Opens recordset based on query 'qryGetLast2QueueRoutings' to get last 2
queue routings
Set rs = db.OpenRecordset("SELECT Queue_Key FROM qryGetLast2QueueRoutings")

'Reads and concatenates last 2 Q routings into temp variable
rs.MoveFirst
temp = rs!Queue_Key 'read first Q routing into variable
rs.MoveNext
temp = temp & rs!Queue_Key 'read second Q routing into same variable
rs.MoveNext
temp = temp & rs!Queue_Key 'added for addition of 4th queue in round robin
11/26/05

If Me.Input05 = "RT14" Then 'If new record is a "system error" request type
then route to Q2
MyQ = 2
Else 'else set variable to whatever Q was not used in the last 2 Q routings
Select Case temp
Case 13, 31
MyQ = 4
Case 14, 41
MyQ = 3
Case 34, 43
MyQ = 1
'added q6 w/ randomly picked numbers 11/26/05
Case 57, 75
MyQ = 6
Case 11
MyQ = 3
Case 33
MyQ = 4
Case 44
MyQ = 1
Case 55
MyQ = 6
'q6 added on 11/26/05
End Select
End If

'set the function value to the chosen Q
SetQueueRouting = MyQ

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function

 

[Guest]

'here is a simple case statement:
Select Case True
case 0 = instr(Temp,1)
MyQ= 1
case 0 = instr(Temp,3)
MyQ= 3
case 0 = instr(Temp,4)
MyQ= 4
case 0 = instr(Temp,5)
MyQ= 5
End select

'here is the same thing as an if statement,
with a rotating entry point so that lower
numbers are not overloaded:
Static n As Integer
Do
temp = Left(temp, 4) 'only last 4 queues
n = n Mod 5 'queues 0 to 4
n = n + 1 'queues 1 to 5
If n = 2 Then n = n + 1 'don't use queue 2
If 0 = InStr(temp, n) Then
myq = n
Exit Do
End If
Loop

 

[Guest]

I tried the second solution listed below which is routing records to the
lists needed except for list 1. I also had to make a slight change as queue
5 is not included in the routing either, which also works fine. Any thoughts
on why its skipping list 1? Here is the updated code I used.

Static n As Integer
Do
temp = Left(temp, 4) 'only last 4 queues
n = n Mod 5 'queues 0 to 4
n = n + 1 'queues 1 to 5
If n = 2 Then n = n + 1 'don't use queue 2 (system issues queue)
If n = 5 Then n = n + 1 'don't use queue 5 (mgr queue)
If 0 = InStr(temp, n) Then
myq = n
Exit Do
End If
Loop

 

[david epsom dot com dot au]

If n = 5 Then n = n + 1 'don't use queue 5 (mgr queue)

This takes it to queue 6

You probably should have put

if n = 5 then n = 1

Or, you can achieve the same affect by changing the mod
statement:

n = n Mod 4 'n = 0 to 3
n = n + 1 'n = 1 to 4
If n = 2 Then n = n + 1 'don't use queue 2 (system issues queue)
If 0 = InStr(temp, n) Then

You can write the first two lines as

n = minQue + (n mod (1 + maxQue- minQue))


However, you need to make sure that the code never
goes into an infinite loop. My code was wrong because
I assumed 5 active queues. You only have 3 active queues,
1,3,4, which means that to ensure that the loop breaks
you must only test the last 2:

temp = Left(temp, 2) 'only last 2 queues
otherwise it might loop for ever on
If 0 = instr("1341",n)

or you can break on a loop count.

Static n As Integer
for I = minQue to MaxQue

n = minQue + (n mod (1 + maxQue- minQue))
If n = 2 Then n = 3 'don't use queue 2 (system issues queue)
If n = 5 Then n = 1 'don't use queue 5 (mgr queue)
If (I=1) or (0 = InStr(temp, n)) Then myq = n
next I

 

[Guest]

Thanks! Now I'm struggling with updating the Data Access Page that performs
the same function for records being added to the database by users that do
not use the database directly. Any idea how to update this code (MS Script)
to get it to also add records to all the lists? I tried using the same code
as you gave me but it didn't work.


Function FetchQueueValue()

Set rs = CreateObject("ADODB.Recordset")
sqlSelect = "SELECT * FROM DAPqry_GetLast2QueueRoutings"
rs.Open sqlSelect, MSODSC.Connection
rs.MoveFirst
temp = rs.fields.item("Queue_Key").value
rs.MoveNext
temp = temp & rs.fields.item("Queue_Key").value
rs.close
set rs = nothing
Select Case temp
Case "13", "31"
FetchQueueValue = 4
Case "14", "41"
FetchQueueValue = 3
Case "34", "43"
FetchQueueValue = 1
Case "11"
FetchQueueValue = 3
Case "33"
FetchQueueValue = 4
Case "44"
FetchQueueValue = 1
End Select

End Function

 

[david epsom dot com dot au]

Select Case temp
Case "13", "31", "33", "23", "32", "53", "35"
FetchQueueValue = 4
Case "14", "41", "11", "15", "51", "21", "12"
FetchQueueValue = 3
Case "34", "43", "44", "45", "54", "24", "42"
FetchQueueValue = 1
case "22"
FetchQueueValue = 1
case "55"
FetchQueueValue = 3
case "25"
FetchQueueValue = 4
case "52"

FetchQueueValue = 1
End Select

(david)

 

[Guest]

Records also need to route to queue 6. Also, when i entered this code and
tested it there was an error on the page showing so i'm not sure it works
properly. Thoughts?

 

[david epsom dot com dot au]

const minQue = 1
const MaxQue = 6
Static n As Integer
for I = minQue to MaxQue

n = minQue + (n mod (1 + maxQue- minQue))
If n = 2 Then n = 3 'don't use queue 2 (system issues queue)
If n = 5 Then n = 6 'don't use queue 5 (mgr queue)
If (I=1) or (0 = InStr(temp, n)) Then myq = n
next I

Records also need to route to queue 6. Also, when i entered this code and
tested it there was an error on the page showing so i'm not sure it works

No, I don't know ASP and I can't see the error message.

(david)

Records also need to route to queue 6. Also, when i entered this code and
tested it there was an error on the page showing so i'm not sure it works
properly. Thoughts?

:

Select Case temp
Case "14", "41", "11", "15", "51", "21", "12", "22", "16", "61"
FetchQueueValue = 3
Case "13", "31", "33", "23", "32", "53", "35", "55", "36", "63"
FetchQueueValue = 4
Case "34", "43", "44", "45", "54", "24", "42", "25"
FetchQueueValue = 6
Case "46", "64", "66", "65", "56", "26", "62", "52"
FetchQueueValue = 1
End Select

 

[Guest]

Will that work in vbscript? If so, where do i insert it? Here's the full
code if it helps. I really appreciate all your help!!!

Function FetchQueueValue()

Set rs = CreateObject("ADODB.Recordset")
sqlSelect = "SELECT * FROM DAPqry_GetLast2QueueRoutings"
rs.Open sqlSelect, MSODSC.Connection
rs.MoveFirst
temp = rs.fields.item("Queue_Key").value
rs.MoveNext
temp = temp & rs.fields.item("Queue_Key").value
rs.close
set rs = nothing
Select Case temp
Case "13", "31"
FetchQueueValue = 4
Case "14", "41"
FetchQueueValue = 3
Case "34", "43"
FetchQueueValue = 1
Case "11"
FetchQueueValue = 3
Case "33"
FetchQueueValue = 4
Case "44"
FetchQueueValue = 1
End Select

End Function

 

[david epsom dot com dot au]

What do you get when you try it in VB script?

(david)

 

[Guest]

It works but it does not assign records evenly. I need the records to be
assigned each value an equal number of times to evenly distribute the records
to be worked. Example: 1, 3, 4, 6, 1, 3, 4, 6,...

I now understand why this code works, I just can't find the even
distribution. I also tried to set up the code in a different manner but
couldn't figure out the statements. I was trying to state:

If Case = 1 Then FetchQueueValue = 3
If Case = 3 Then FetchQueueValue = 4
If Case = 4 Then FetchQueueValue = 6
If Case = 6 Then FetchQueueValue = 1
If Case = 2 Then MoveFirst and add 1
If Case = 5 Then MoveFirst and add 1

Thanks for all your help...I know we're sooo close!

 

[David C. Holley]

I haven't been entirely following this thread, but if you're trying to
evenly distribute the records, that will involve taking the total number
of records to be distributed and dividing them by the number of
assignees. Along with that you'll have to figure out how to deal with
situations where the records can't be distributed evenly (eg 10 records
being assigned to 3 persons will result in 1 person having 4 records and
the other having 3 records each.)