Add a pattern of sequential numbers to a query (3 Viewers)

[jco23]

How can I add a field to an existing query to include a sequential list of numbers (e.g. 1, 2, 3, 4) repeating?

for example, I want to add an "Assignment" column. This column corresponds with another query where we have four people assigned one of these four numbers (some days the list may be five, other days it could be three).

The idea is that we want to evenly distribute the task list to this group. Many times, this list can get well into the hundreds, and it would be unfair if one person is assigned significantly more tasks than another person.

see below as an example of what I'm trying to achieve.

if it helps, the data is coming from a temp local table, which will then be appended to a SQL server for multiple users. Not sure if I can create an autonumber field and have it tied to the query of assignees to determine how high the sequence of numbers should go.

thanks!

Field1 Field2 Field3 Assignment
a 123 Chicago 1
b 231 LA 2
c 456 New York 3
d 796 Atlanta 4
e 5423 Baltimore 1
f 451 Toronto 2
g 33 Austin 3
h 12 Miami 4
i 19085 Seattle 1
j 4536 Phoenix 2

apologies for the poorly formatted table, but when I tried to copy and paste from Excel, it left off the data from the two right columns.

​

 

[MajP]

Are you open to a code suggestion where you push the results into a temp table. The reason is you can do a query that pulls a random number between 1 and N, but that will assign the assignments randomly, but not ensure that the groups are as equal as possible. They may be close to equal but not equal.

 

[jco23]

Are you open to a code suggestion where you push the results into a temp table. The reason is you can do a query that pulls a random number between 1 and N, but that will assign the assignments randomly, but not ensure that the groups are as equal as possible. They may be close to equal but not equal.

yes, I'm good pushing the results to a temp table. I've tried the random number, but I want to ensure that they there are evenly distributed as possible.

 

[RonPaii]

If you want the repeating sequence, the following function will work. Before opening the query execute the function passing the max value in the sequence. In the query use the same function passing it null.

Public Function CountUpRepeating(vResetCount As Variant) As Long
    On Error GoTo errCountUpRepeating
    Static IntCount As Long
    Static MaxCount As Long
    If Not IsNumeric(vResetCount) Then
        IntCount = IntCount + 1
        If IntCount > MaxCount Then
            IntCount = 1
        End If
    Else
        MaxCount = CLng(vResetCount)
        IntCount = 0
    End If
doneCountUpRepeating:
    CountUpRepeating = IntCount
    Exit Function
errCountUpRepeating:
    Debug.Print "CountUpRepeating error)" & Err.Number & ") " & Err.Description
    Resume doneCountUpRepeating
End Function

Test run in immediate window

CountUpRepeating(4)
?CountUpRepeating(null)
 4
 3
 2
 1
 4
 3
 2
 1

 

[jco23]

If you want the repeating sequence, the following function will work. Before opening the query execute the function passing the max value in the sequence. In the query use the same function passing it null.

Public Function CountUpRepeating(vResetCount As Variant) As Long
    On Error GoTo errCountUpRepeating
    Static IntCount As Long
    Static MaxCount As Long
    If Not IsNumeric(vResetCount) Then
        IntCount = IntCount + 1
        If IntCount > MaxCount Then
            IntCount = 1
        End If
    Else
        MaxCount = CLng(vResetCount)
        IntCount = 0
    End If
doneCountUpRepeating:
    CountUpRepeating = IntCount
    Exit Function
errCountUpRepeating:
    Debug.Print "CountUpRepeating error)" & Err.Number & ") " & Err.Description
    Resume doneCountUpRepeating
End Function

Test run in immediate window

CountUpRepeating(4)
?CountUpRepeating(null)
 4
 3
 2
 1
 4
 3
 2
 1

i see that working in the immediate window, but when I add it as a field to a query, I just get all zeros.

when I add this line, "CountUpRepeating (DMax("[order]", "[qry_assignees]")) and then add the command to open the query with the countuprepeating(null) as a field, I get all ones.

 

[RonPaii]

i see that working in the immediate window, but when I add it as a field to a query, I just get all zeros.

when I add this line, "CountUpRepeating (DMax("[order]", "[qry_assignees]")) and then add the command to open the query with the countuprepeating(null) as a field, I get all ones.

You have to pass it a value from a control, otherwise the query processor with not call it.

CountUpRepeating(IIf([Field1 ],Null,Null)) as Assignment

 

[jco23]

thanks for your replies, @RonPaii !!!

I appreciate it, you gave me some ideas on how to eventually get to my desired results.

I think I got it using this:

Assignee_Num: (((1+DCount("*","[mytable]","[field1]< " & [mytable].[field1])+DCount("*","[mytable]","[field1] = " & [mytable].[field1] & " and field2 <= " & [mytable].[field2])-1) Mod DMax("[order_num]","[qry_assignees]"))+1)

 

[RonPaii]

thanks for your replies, @RonPaii !!!

I appreciate it, you gave me some ideas on how to eventually get to my desired results.

I think I got it using this:

Assignee_Num: (((1+DCount("*","[mytable]","[field1]< " & [mytable].[field1])+DCount("*","[mytable]","[field1] = " & [mytable].[field1] & " and field2 <= " & [mytable].[field2])-1) Mod DMax("[order_num]","[qry_assignees]"))+1)

All those DCount and DMax calls will be very slow in a query, but workable in a small dataset.

 

[MajP]

Assuming you do not always have equal groups you would want to make sure that employee x is not always getting the remainder so you would want to assign them each time in a different order.

Public Sub AssignRandom()
 
  Dim EmployeeCount As Integer
  Dim CityCount As Integer
  Dim EqualAssignments As Integer
  Dim Remainder As Integer
  Dim strSql As String
  Dim rsEmployees As DAO.Recordset
  Dim rsAssignments As DAO.Recordset
 
  EmployeeCount = DCount("*", "tblEmployees")
  CityCount = DCount("*", "tblCities")
  EqualAssignments = CityCount \ EmployeeCount
  Remainder = CityCount Mod EmployeeCount
 
  Debug.Print CityCount & " " & EmployeeCount & " " & EqualAssignments & " " & Remainder
 
  'Empty assignments
  strSql = "Delete * from tblAssignments"
  CurrentDb.Execute strSql
  'Insert cities back in
  strSql = "INSERT INTO tblAssignments (cityID_FK) SELECT tblCities.CityID FROM tblCities order by myRnd([cityID])"
  CurrentDb.Execute strSql, dbFailOnError
  'Assign the employees in random order so the persons getting the remainder are not always the same.
  Set rsAssignments = CurrentDb.OpenRecordset("tblAssignments")
  Set rsEmployees = CurrentDb.OpenRecordset("Select EmployeeID from tblEmployees order by myRnd([employeeID])")
 
 
   Do While Not rsAssignments.EOF
     rsEmployees.MoveFirst
     Do While Not rsEmployees.EOF
       If rsAssignments.EOF Then Exit Sub
       rsAssignments.Edit
         rsAssignments!employeeID_FK = rsEmployees!EmployeeID
       rsAssignments.Update
        rsEmployees.MoveNext
       rsAssignments.MoveNext
     Loop
   Loop
End Sub



Public Function myRnd(id As Variant) As Double
  If Not IsNull(id) Then
    Randomize
    myRnd = Rnd(Now() + id)
  End If
End Function

So this code clears out the temp table of Assignments. Populates the temp table with all the cities. Then grabs the employees in random order and starts assigning to them. So this way no person is ever always getting the largest group.

In this case there is 5 employees, and 203 cities. So on some days some people would gets 41 cities and others 40. But who gets the large group is random.

There was a potential mistake in the original version. By not entering the cities in a random order the employees would get a random set of cities, but those sets repeat. Now the employee gets a random set of cities each time and random assignment of which three get the extra city. This version has the fix and a form so you can see this work