Solved Query to only offer 20 records (1 Viewer)

Number11

Member
Local time
Today, 15:39
Joined
Jan 29, 2020
Messages
607
Hi,

So i am looking for a way that the query will only offer the end user a max of 20 records for them to edit, and then the next user gets another 20 records for themself, is thi possible at all?
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:39
Joined
Sep 21, 2011
Messages
14,044
Have a field for username and update 20 for each user and then present the records for the relevant user.?
This way if any are not completed you can assign remaining to another user.?
 

Number11

Member
Local time
Today, 15:39
Joined
Jan 29, 2020
Messages
607
Have a field for username and update 20 for each user and then present the records for the relevant user.?
This way if any are not completed you can assign remaining to another user.?

sounds like that might work, so when user opens the form in the background it allocates them 20 records and shows then in the form which some how is controlled by user name?, good idea i was just thinking when query runs it only allocated a max of 20 at a time, as the user update the records it gets time stamped with user name and date it was completed, so then does not get shown in future
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:39
Joined
Sep 21, 2011
Messages
14,044
I had to do something similar to allocate work to multiple users for the day. Some had the authorisation for large amounts, so they had to be allocated correctly as well.
Your way would work, but how do you identify the 20 records already allocated.?
You could just update 20 records with username and present to the user. When record is completed only then does it get a timestamp.? That way you can reallocate any not completed.
I also gave the team leaders the option of taking a number of records off one user and allocate to another.
 

Number11

Member
Local time
Today, 15:39
Joined
Jan 29, 2020
Messages
607
I had to do something similar to allocate work to multiple users for the day. Some had the authorisation for large amounts, so they had to be allocated correctly as well.
Your way would work, but how do you identify the 20 records already allocated.?
You could just update 20 records with username and present to the user. When record is completed only then does it get a timestamp.? That way you can reallocate any not completed.
I also gave the team leaders the option of taking a number of records off one user and allocate to another.

sounds good so how would the query look etc.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:39
Joined
Sep 21, 2011
Messages
14,044
I actually used the queries as recordsets due to the complexity of the assignments plus my inexperience with Access.
One recordset for unassigned records, another for unassigned users and allocated as required. The user table held how many to allocate and how many allocated. The TLs controlled these values from a form.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:39
Joined
Feb 28, 2001
Messages
26,999
It seems like this is a call processing center or a similar situation. With the U.S.Navy's Naval Enterprise Data Center we had a huge "Help Desk" and what they did was one call at a time. I have to approximate this because I was a Tier III responder, not a maintainer, so I didn't see the actual SQL of this situation.

Basically there was a list of things needing attention, each one time-tagged with an arrival date and time. Each of those things was in a particular "call state." This included "Initial entry" and "Forwarded to Tier II" and "Waiting for callback" and a bunch of other statuses. When an agent became available, they always looked for the oldest unassigned call awaiting first screening - one call at a time. The agent would get a list of available calls and could skip one if the stated topic was not within their expertise. They would accept the call and thus make it "theirs." At which point it was no longer "unassigned." The agents would typically take the "Initial entry" calls in oldest-first order. A.k.a. "FIFO" or "first in, first out." If they could handle it, they DID. If it required expertise they would annotate that and fill in a selection (drop-down) of categories of expertise and then mark it as requiring Tier II attention. When it required special action that only an administrator could provide, it would be forwarded to Tier III people, all of whom had other duties as well. This might be more complex than you need, but the idea is fairly simple to implement for simple situations.

You need an item status, a date of last processing, and the name/ID of the person to whom the item was assigned (or blank, meaning unassigned.)
 

Number11

Member
Local time
Today, 15:39
Joined
Jan 29, 2020
Messages
607
So this seems to be much trickier than first thought - any examples would be great :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:39
Joined
Sep 21, 2011
Messages
14,044
Break it down into smaller steps.

Here is my allocate code, run from a button on the Daily Allocation Form.

Perhaps it will help?

Code:
Private Sub cmdAllocate_Click()
On Error GoTo Err_Handler

Dim dbsPA As Database
Dim rstWA As Recordset
Dim rstPA As Recordset
Dim strSQLWA As String, strSQLPA As String
Dim strProduct As String, strMethod As String, StrQueue As String, strUser As String, strProgress As String
Dim intAllocate As Integer, intLoop As Integer, lngTotalAllocated As Long
Dim curLimit As Long, curAmount As Long
Dim blnShort As Boolean



'Set dbsPA = OpenDatabase("PA_Allocation")
Set dbsPA = CurrentDb()

'SQL statement to get Payment Analysts

strSQLPA = "SELECT Analyst.File_ID, Analyst.Full_Name, Analyst.Queue, Analyst.Product, Analyst.Pay_Method, Analyst.Workstream, Analyst.Allocation, Analyst.Received, Analyst.Working "
strSQLPA = strSQLPA & "FROM Analyst WHERE (((Analyst.Working)=True)) ;"

' SQL statement to get data to be allocated

Set rstPA = dbsPA.OpenRecordset(strSQLPA)

'We are progressing, so open Progress form that will act as a status update to user

'DoCmd.OpenForm "Progress"
'Forms!Progress.txtProgress.Value = " Starting Allocation..."
'Forms!Progress.Refresh

Do While Not rstPA.EOF
' First get the extra criteria for the recordset

    strProduct = Chr$(39) & rstPA.Fields("Product") & Chr$(39)
    strMethod = Chr$(39) & rstPA.Fields("Pay_Method") & Chr$(39)
    StrQueue = Chr$(39) & rstPA.Fields("Queue") & Chr$(39)
    strUser = rstPA.Fields("Full_Name")
    intAllocate = rstPA.Fields("Allocation")
' Payment limits are £5000 or £100,000
    If rstPA.Fields("Workstream") = "Under 5K" Then
        curLimit = 5000
    Else
        curLimit = 100000
    End If
    
strSQLWA = "SELECT Daily_Work_Allocation.Amount, Daily_Work_Allocation.Process_Payment_Cashiers_allocated_to_name, Daily_Work_Allocation.payment_method, Daily_Work_Allocation.product_01, Daily_Work_Allocation.Payment_Case_Awaiting_Payment_allocated_to_name FROM Daily_Work_Allocation "
strSQLWA = strSQLWA & "WHERE (((Daily_Work_Allocation.Payment_Case_Awaiting_Payment_allocated_to_name) = '') AND ((Daily_Work_Allocation.Process_Payment_Cashiers_allocated_to_name)= " & StrQueue & ") AND ((Daily_Work_Allocation.payment_method)=" & strMethod & ") AND ((Daily_Work_Allocation.product_01)=" & strProduct & ")"
strSQLWA = strSQLWA & " AND ((Daily_Work_Allocation.Status) = '" & Me.cmbStatus & "'));"

    ' MsgBox strSQLWA
    
    Set rstWA = dbsPA.OpenRecordset(strSQLWA, dbOpenDynaset)
    
    intLoop = 0
    If Not rstWA.EOF Then
        rstWA.MoveFirst
      Else
        MsgBox "No payments found for " & strProduct & " " & strMethod & " for queue " & StrQueue
    End If
       
'    Debug.Print rstWA.RecordCount
    Do While intLoop < intAllocate And Not rstWA.EOF
        curAmount = rstWA.Fields("amount")
' Check the PA is authorised for the amount of payment
        If curAmount <= curLimit Then
            With rstWA
                .Edit
                .Fields("Payment_Case_Awaiting_Payment_allocated_to_name").Value = strUser
                .Update
            End With
            intLoop = intLoop + 1
        End If
        rstWA.MoveNext
    Loop
        lngTotalAllocated = lngTotalAllocated + intLoop
'Now update Analyst table with amount allocated.
    With rstPA
        .Edit
        .Fields("Received").Value = .Fields("Received").Value + intLoop
        .Update
    End With

' Now close WA ready for next select
    rstWA.Close
    rstPA.MoveNext
Loop

' Now refresh the Crosstab datasheet
' Me.[Daily_Work_Allocation_Crosstab_subform].Form.Refresh
    
    Set dbsPA = Nothing
    Set rstPA = Nothing
    Set rstWA = Nothing
    

Me.Refresh
MsgBox lngTotalAllocated & " payments allocated..."

Err_Exit:
    Exit Sub
    
Err_Handler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume Err_Exit

End Sub
 

Number11

Member
Local time
Today, 15:39
Joined
Jan 29, 2020
Messages
607
OK, so thinking about this approach.
1. User clicks on "Button1" which runs an append query to only copy 20 of the oldest records to a new Table called "Allocated_Work" then run a second query to delete the same 20 records from the main table, so no other users get the same records.
Then once the 20 records have been actioned/updated anothe append query to move back the 20 records to the main table and then a second delete query to clear "Allocated-Work table, so only issue is how do i get the query to only select a max of 20 records?
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:39
Joined
Sep 21, 2011
Messages
14,044
No, just put the username in a field to show they are allocated (or some other method) and always look for records with this field empty.
So a query would get/update top 20 sorted descending not allocated and then the form selects not allocated for a certain username.

This is not a simple task, so plan your steps carefully.
My DB was over 5 years ago and I cannot remember a lot about it now. The only reason I have some code is that I would work on it at home in the evenings. :D
 

Number11

Member
Local time
Today, 15:39
Joined
Jan 29, 2020
Messages
607
No, just put the username in a field to show they are allocated (or some other method) and always look for records with this field empty.
So a query would get/update top 20 sorted descending not allocated and then the form selects not allocated for a certain username.

This is not a simple task, so plan your steps carefully.
My DB was over 5 years ago and I cannot remember a lot about it now. The only reason I have some code is that I would work on it at home in the evenings. :D

Ok so i see, i have added a complete by field, on clicking button i need to run an update query to enter the users name into the records however how do i just limit to 20 records? ok sorted it now i can limit the records to 25 :)
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 15:39
Joined
Sep 21, 2011
Messages
14,044
Make the number come from a form, so if someone is just working the morning you can allocate a smaller number.?
What happens if someone has an emergency and has to go home? How you reallocate what has not been worked to someone else.

It is situations like this you need to think of?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:39
Joined
Feb 28, 2001
Messages
26,999
If you don't have a major issue with network speed, allocate one at a time. Allocating 20 or 25 at a time doesn't save you that much network bandwidth in the long haul and makes it easier to do the logic... which is:

A person clicks the "Allocate next item" button. The code behind that button identifies the unallocated work item with the earliest date/time. The code then attempts to place the agent's ID in the "AssignedTo" field of the selected work item, which must initially have been blank in order to be unallocated in the first place.

Now, still behind the same button, you execute a DoEvents (to allow the system to "settle") and then TEST for whether a work item now has that person's name on it. If so, the assignment was a success. If not, someone else got there first, perhaps even by a few milliseconds. Doesn't matter WHO gets the problem, only that someone gets it. So... if the allocation worked, you are done and that person has a work item. If the allocation failed, just go back up to the top and do it again. Of course, test after each attempt to allocate a work item to see if the query found an eligible entry. If ever you get back a "0 records found" then you might be caught up with your backlog.

The only important part if you took this approach would be that when changing the state of a work-item to give it to someone else, you have to blank out the "AssignedTo" field.
 

Users who are viewing this thread

Top Bottom