allocating new clients based on the least number 1

[shaunacol]

I need to automate the process of allocating new leads to salespeople. Batches of new leads come in regularly and I just want MS Access to take each one at a time then look at the salesperson with the least number of active leads and allocated it to them. My thinking is to create a query couting the number of leads per person and whoever has the least gets the next one. Any thoughts or help appreciated! Each lead has a 'salesperson' field which would need to be updated, is my only option an update query?

 

[Golom]

SQL has problems with getting data from a table or tables when it is simultaneously modifying data in those tables. For that reason you need to do this with a combination of VBA and SQL.

Public Sub AssignSalesPeople()
Dim db                          As DAO.Database
Dim ld                          As DAO.Recordset
Dim sp                          As DAO.Recordset

On Error GoTo BadNews

Set db = CurrentDB

' Get the Leads that do not have a Sales Person assigned.
Set ld = db.OpenRecordset ( _
         "Select LeadNum, SalesPerson " & _
         "From Leads Where Trim$(SalesPerson & '') = ''", dbOpenSnapShot )

Do Until ld.EOF

   ' Find the Sales Person with the fewest leads. If there's a tie then
   ' just arbitrarily pick one.
   Set sp = db.OpenRecordset ( _
            "Select S.SalesPerson " & _
            " From SalesPersons As S LEFT JOIN Leads As L " & _
            "      ON S.SalesPerson = L.SalesPerson " & _
            " GROUP BY S.SalesPerson " & _
            " ORDER BY Count(L.SalesPerson)" )
   
   ' Assign this lead to the salesperson. 
   db.Execute ("UPDATE Leads Set SalesPerson = '" & _
               Replace(sp![SalesPerson], "'", "''") & "' " & _
              "WHERE LeadNum = " & ld.LeadNum ), dbFailOnError

   ld.MoveNext
Loop

Exit_Sub:
Set ld = Nothing
Set sp = Nothing
Set db = Nothing
Exit Sub

BadNews:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Sub:

End Sub

I have invented a field "LeadNum" as a key field in the "Leads" table.

 

[shaunacol]

This looks amazing! Exactley what I am looking for. Cant thank you enough. Now I just need to get it to work! i will let you know if I have any issues. Thanks again. Shauna

 

[Golom]

Just a couple of minor corrections to fix wrong syntax.

Public Sub AssignSalesPeople()
Dim db                          As DAO.Database
Dim LD                          As DAO.Recordset
Dim SP                          As DAO.Recordset

On Error GoTo BadNews

Set db = CurrentDb

' Get the Leads that do not have a Sales Person assigned.
Set LD = db.OpenRecordset( _
         "Select LeadNum " & _
         "From Leads Where Trim$(SalesPerson & '') = ''", dbOpenSnapshot)

Do Until LD.EOF

    ' Find the Sales Person with the fewest leads. If there's a tie then
    ' just arbitrarily pick one.
    Set SP = db.OpenRecordset( _
             "Select S.SalesPerson " & _
             " From SalesPersons As S LEFT JOIN Leads As L " & _
             "      ON S.SalesPerson = L.SalesPerson " & _
             " GROUP BY S.SalesPerson " & _
             " ORDER BY Count(L.SalesPerson)")

    ' Assign this lead to the salesperson.
    db.Execute "UPDATE Leads Set SalesPerson = '" & _
               Replace(SP![SalesPerson], "'", "''") & "' " & _
               "WHERE LeadNum = " & LD![LeadNum] & ";", dbFailOnError


    LD.MoveNext
Loop

Exit_Sub:
Set LD = Nothing
Set SP = Nothing
Set db = Nothing
Exit Sub

BadNews:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Sub:

End Sub

 

[shaunacol]

Hi Golom, I am finally at the point of making this work but due to my lack of programming knowledge I am having problems understanding how it works. Can you help me please? I am getting a syntax error in the part where it searches for salespeople with fewest leads. Not sure what I have done wrong, any help appreciated thanks.



Private Sub Allocate_Click()

Dim db As DAO.Database
Dim LD As DAO.Recordset
Dim SP As DAO.Recordset

On Error GoTo BadNews

Set db = CurrentDb

' Get the Leads that do not have a Sales Person assigned.
Set LD = db.OpenRecordset( _
"Select ClientID " & _
"From Tbl_Client Where Trim$(StaffAllocated & '') = ''", dbOpenSnapshot)

Do Until LD.EOF

' Find the Sales Person with the fewest leads. If there's a tie then
' just arbitrarily pick one.
Set SP = db.OpenRecordset( _
"Select StaffName " & _
" From Tbl_Staff As S LEFT JOIN Leads As L " & _
" ON S.Staffname = L.Staffname " & _
" GROUP BY S.Staffname " & _
" HAVING (Tbl_client.status) = 2 & _
" ORDER BY Count(L.Staffname)



' Assign this lead to the salesperson.
db.Execute "UPDATE Tbl_Client Set StaffAllocated = '" & _
Replace(SP![StaffAllocated], "'", "''") & "' " & _
"WHERE ClientID = " & LD![Clientid] & ";", dbFailOnError


LD.MoveNext
Loop

Exit_Sub:
Set LD = Nothing
Set SP = Nothing
Set db = Nothing
Exit Sub

BadNews:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Sub:

End Sub

 

[Golom]

You were missing a quote and parentheses

Set SP = db.OpenRecordset( _
                 "Select StaffName " & _
                 " From Tbl_Staff As S LEFT JOIN Leads As L " & _
                 " ON S.Staffname = L.Staffname " & _
                 " GROUP BY S.Staffname " & _
                 " HAVING (Tbl_client.status) = 2 [red]"[/red] & _
                 " ORDER BY Count(L.Staffname) "[red])[/red]

I noticed that you have

[blue]HAVING (Tbl_client.status) = 2[/blue]

but [blue]Tbl_client[/blue] does not appear in your FROM clause.

If you really want to limit the clients to Status = 2 then drop that HAVING clause and change the SQ for recordset LD to

Set LD = db.OpenRecordset( _
         "Select ClientID " & _
         "From Tbl_Client Where Trim$(StaffAllocated & '') = '' " & _
         [red]" AND Status = 2 "[/red], dbOpenSnapshot)

 

[shaunacol]

I think you are onto somethign in regards to my HAVING statement. I have taken it out altogether in order to get this working first then I can put it in later. So now I am getting the error "3061 Too few paramters. Expected 1."
Below is the codee and how I changed it. This error is worst than the last as I have done some research and no idea!

Private Sub Allocate_Click()

Dim db As DAO.Database
Dim LD As DAO.Recordset
Dim SP As DAO.Recordset

On Error GoTo BadNews

Set db = CurrentDb

' Get the Leads that do not have a Sales Person assigned.
Set LD = db.OpenRecordset( _
"Select ClientID " & _
"From Tbl_Client Where Trim$(StaffAllocated & '') = ''", dbOpenSnapshot)

Do Until LD.EOF

' Find the Sales Person with the fewest leads. If there's a tie then
' just arbitrarily pick one.
Set SP = db.OpenRecordset( _
"Select StaffName " & _
" From Tbl_Staff As S LEFT JOIN Tbl_client As L " & _
" ON S.Staffname = L.Staffname " & _
" GROUP BY S.Staffname " & _
" ORDER BY Count(L.Staffname)")


' Assign this lead to the salesperson.
db.Execute "UPDATE Tbl_Client Set StaffAllocated = '" & _
Replace(SP![StaffAllocated], "'", "''") & "' " & _
"WHERE ClientID = " & LD![Clientid] & ";", dbFailOnError


LD.MoveNext
Loop

Exit_Sub:
Set LD = Nothing
Set SP = Nothing
Set db = Nothing
Exit Sub

BadNews:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Sub:

End Sub

 

[Golom]

First of all, [blue]Select StaffName[/blue] is going to have some problems because you have two tables with a field named "StaffName". You should specify

"Select [red]S.[/red]StaffName" or "Select [red]L.[/red]StaffName"

That error usually means that you have specified a field name that doesn't exist in your tables. You have, for reasons that are unclear to me, dropped the table "Leads" and substituted "Tbl_Client". That table probably doesn't have a field named "StaffName" so you are getting the error. You should probably be JOINing to table "Leads" anyway.

Another issue (after you have corrected that problem) is that the recordset SP is returning a field called "StaffName". In the UPDATE SQL following that however, you are referencing a field called "SP![StaffAllocated]" which does not now exist. It should probably be "SP![StaffName]

 

[shaunacol]

Oh dear, sounds like I have made a mess but my database seems to be set up very differently so let me explain:
I have a table called Tbl_client which has details of all the leads in (all leads are treated as clients in this company). StaffAllocated is a field in Tbl_client which contains the number of the person allocated (FK). The PK of Tbl_Client is ClientID. The other table (Tbl_Staff) contains a number field (PK) called StaffName. So, as you can see, StaffName does not appear in more than one table. I think this is what is so confusing. I think I am struggling to understand the syntax but have tried again and am now getting error "3265 - item not found in this collection" which makes me think I have gotten something the wrong way around....any help appeciated and thanks for the tips so far, I am def getting there thanks to your help.


Private Sub Allocate_Click()

Dim db As DAO.Database
Dim LD As DAO.Recordset
Dim SP As DAO.Recordset

On Error GoTo BadNews

Set db = CurrentDb

' Get the Leads that do not have a Sales Person assigned.
Set LD = db.OpenRecordset( _
"Select ClientID " & _
"From Tbl_Client Where Trim$(StaffAllocated & '') = ''", dbOpenSnapshot)

Do Until LD.EOF

' Find the Sales Person with the fewest leads. If there's a tie then
' just arbitrarily pick one.
Set SP = db.OpenRecordset( _
"Select StaffName " & _
" From Tbl_Staff As S LEFT JOIN Tbl_client As L " & _
" ON S.StaffName = L.Staffallocated " & _
" GROUP BY S.StaffName " & _
" ORDER BY Count(L.Staffallocated)")


' Assign this lead to the salesperson.
db.Execute "UPDATE Tbl_Client Set StaffAllocated = '" & _
Replace(SP![L.StaffAllocated], "'", "''") & "' " & _
"WHERE StaffName = " & LD![staffallocated] & ";", dbFailOnError


LD.MoveNext
Loop

Exit_Sub:
Set LD = Nothing
Set SP = Nothing
Set db = Nothing
Exit Sub

BadNews:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Sub:

End Sub

 

[Golom]

You have

db.Execute "UPDATE Tbl_Client Set StaffAllocated = '" & _
Replace([red]SP![L.StaffAllocated][/red], "'", "''") & "' " & _
WHERE StaffName = " & [red]LD![staffallocated][/red]

You are referencing [red]SP![L.StaffAllocated][/red] but recordset "SP" returns a field named "StaffName". Similarly, "LD" is the recordset whose only field is "ClientID". It can't find (i.e. item not found in this collection) "StaffAllocated" in SP, nor can it find "StaffAllocated" in "LD".

Another note ...
Inside the definition of fields in a recordset, the fields are commonly qualified by their table name (e.g [red]L.[/red]Staffallocated). When you reference them as field names in a recordset however, they do not have the table name or alias qualifier. There are some occasions where they do but this is not one of those times.

Your explanation however does occasion some rethinking of how this should be done. The code was designed to use a (now mythical) table called "Leads" and, based on that, update Tbl_Client with a sales person. Now the table containing the lead and the table to be updated are the same table, specifically, Tbl_Client.

This code does the following.

- Gets a list of the clients who have no one allocated (recordset LD).

- Joins the Tbl_Staff and Tbl_Client tables to find the sales person with the fewest assigned leads (recordset SP).

- Modifies the Tbl_Client to assign the sales person found in SP to that lead.

Private Sub Allocate_Click()

Dim db                         As DAO.Database
Dim LD                         As DAO.Recordset
Dim SP                         As DAO.Recordset

On Error GoTo BadNews

Set db = CurrentDb

[blue]' Get the Leads that do not have a Sales Person assigned.[/blue]
Set LD = db.OpenRecordset( _
         "Select ClientID " & _
         "From Tbl_Client " & _
         "Where Trim$(StaffAllocated & '') = ''", dbOpenSnapshot)

Do Until LD.EOF

    [blue]' Find the Sales Person with the fewest leads. If there's a tie then[/blue]
    [blue]' just arbitrarily pick one.[/blue]
    Set SP = db.OpenRecordset( _
             "Select StaffName " & _
             " From Tbl_Staff As S LEFT JOIN Tbl_Client As C " & _
             " ON S.StaffName = C.StaffAllocated " & _
             " GROUP BY S.StaffName " & _
             " ORDER BY Count(C.StaffAllocated)")

    [blue]' Assign this lead to the salesperson. The field StaffAllocated is[/blue]
    [blue]' updated to the sales person found in recordset SP. AND we are[/blue]
    [blue]' doing that to the Tbl_Client record for the client LD![ClientID][/blue]
    db.Execute "UPDATE Tbl_Client Set StaffAllocated = '" & _
               Replace(SP![StaffName], "'", "''") & "' " & _
               "WHERE ClientID = " & LD![ClientID] & ";", dbFailOnError
Loop

Exit_Sub:
Set LD = Nothing
Set SP = Nothing
Set db = Nothing
Exit Sub

BadNews:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Sub:

End Sub

 

[shaunacol]

Thanks so much for taking the time to explain the code. I have used the code but unfortunately it hangs and im not sure why. I have it behind a button so when I press the button it hangs. However...it does work as one of the leads is allocated with a salesperson. There are 8 salespeople in my table and 6 of them have some leads allocated. The code finds the 7th person and allocateds their name to the next lead. I can see this has happened after I close down the database and open it up again. Ive tried leaving it quite a while before closing it down so I know it is hanging. I will now try to step through the code to see if I can work out what happens. Thanks again and do let me know if yu have any ideas!

 

[Golom]

Its because I screwed up ... sorry about that

Do Until LD.EOF

    [blue]existing code[/blue]

    [red]LD.MoveNext[/red]
Loop

 

[shaunacol]

You are a genius!! It works!!!

 

[shaunacol]

I am so grateful to get to this point, but I need to tweak 3 further things....

1. On my form I am listing the Staff names from Tbl_Staff and also a field called 'Available' (a tickbox) along with the button that has my code behind it. Staff can only be allocated the leads if they are available.
2. I want the field 'FirstContact' from Tbl_Client to updated to the same value as 'StaffAllocated' at the same time.
3. I need the code to only count the number of active leads (i.e. all clients with a status of 2).

I understand the code now thanks to Golom but I just dont understand the " and _ and & and all the syntax. So I am looking for something along these lines:


' Find the Sales Person with the fewest leads. If there's a tie then
' just arbitrarily pick one.
Set SP = db.OpenRecordset( _
"Select ID[highlight #CC0000], available[/highlight] " & _
" From Tbl_Staff As S LEFT JOIN Tbl_Client As C " & _
" ON S.ID = C.StaffAllocated " & _
[highlight #CC0000]"WHERE Available = yes " & _[/highlight]
" GROUP BY S.ID " & _
" ORDER BY Count(C.StaffAllocated)")

' Assign this lead to the salesperson. The field StaffAllocated is
' updated to the sales person found in recordset SP. AND we are
' doing that to the Tbl_Client record for the client LD![ClientID]
db.Execute "UPDATE Tbl_Client Set StaffAllocated[highlight #CC0000]& FirstContact[/highlight] = '" & _
Replace(SP![ID], "'", "''") & "' " & _
"WHERE ClientID = " & LD![ClientID] & [highlight #CC0000]status = 2[/highlight]";", dbFailOnError

 

[PHV]

Set SP = db.OpenRecordset( _
"SELECT S.ID" & _
" FROM Tbl_Staff As S LEFT JOIN Tbl_Client As C " & _
" ON S.ID = C.StaffAllocated " & _
" WHERE S.Available = yes " & _
" GROUP BY S.ID " & _
" ORDER BY Count(C.StaffAllocated)")

Dim strID As String
strID = Replace(SP![ID], "'", "''")
db.Execute "UPDATE Tbl_Client" & _
" SET StaffAllocated='" & strID & "'" & _
",FirstContact='" & strID & "'" & _
" WHERE ClientID=" & LD![ClientID] & " AND status=2", dbFailOnError

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shaunacol]

That is fab but it allocates salespeople to active leads only whereas I want to count the number of active leads they have allocted already and then allocate a salesperson to any leads that have no one allocated. My fault as I put the status in the wrong part of the code:

Set SP = db.OpenRecordset( _
"SELECT S.ID" & _
" FROM Tbl_Staff As S LEFT JOIN Tbl_Client As C " & _
" ON S.ID = C.StaffAllocated " & _
" WHERE S.Available = yes "" [highlight #FCE94F]AND status=2", & _[/highlight]" GROUP BY S.ID " & _
" ORDER BY Count(C.StaffAllocated)")

Dim strID As String
strID = Replace(SP![ID], "'", "''")
db.Execute "UPDATE Tbl_Client" & _
" SET StaffAllocated='" & strID & "'" & _
",FirstContact='" & strID & "'" & _
" WHERE ClientID=" & LD![ClientID] & dbFailOnError

 

[PHV]

SELECT S.ID" & _
" FROM Tbl_Staff As S LEFT JOIN Tbl_Client As C" & _
" ON S.ID=C.StaffAllocated" & _
" WHERE S.Available=yes AND status=2" & _
" GROUP BY S.ID" & _
" ORDER BY Count(C.StaffAllocated)")


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

I don't have a firm grasp of the difference between "active" and "available". Certainly "available" corresponds to your check box which is a field in the table. Are we to assume that "Active" means that they already have at least one client assigned?

In an earlier discussion you had attempted to use

" HAVING (Tbl_client.status) = 2 " & _

We decided that there were problems with that but the implication was clearly that "Status" is a field in the Tbl_Client table. If it is then the effect of placing a condition on the RIGHT table in a LEFT JOIN is to turn the LEFT JOIN into an INNER JOIN. That means that your SQL will NOT return any sales people who have zero clients assigned.

If you understand why that happens that's great. If you don't then just ask and I'll go through the logic of INNER and OUTER joins for you.

If you want to constrain the clients being processed to those where Status = 2 then you need to

- Remove "AND Status = 2" from this query (above)
- Modify the LD query to

' Get the Leads that do not have a Sales Person assigned.
Set LD = db.OpenRecordset( _
         "Select ClientID " & _
         "From Tbl_Client " & _
         "Where Trim$(StaffAllocated & '') = '' " & _
         [red]"  AND Status = 2 "[/red], dbOpenSnapshot)

Then you are processing only those clients that have Status = 2

 

[shaunacol]

Both, your help is very much appreciated. Let me explain. 'Available' is in Tbl_Staff and it dictates whether staff are available to be able to accept any leads that day (i.e. if they are in office). Status is in Tbl_Client and dictates whether the leads that are allocated to members of staff are 'active' or 'dead' etc. So my thinking is that when I am counting how many leads each person already has (to decide the next person to allocate to) then I must only count the active leads otherwise if I count all the leads allocated to them in Tbl_client many of them will be dead. This isnt giving me an accurate picture of how much work they have on or allocating leads fairly. I hope this helps explain. I am going to try the code from you both above and see if I can get it to work.

 

[Golom]

I finally broke down and set up some test tables and, with your explanation, came up with an issue.

Suppose that a sales person is available but they are currently assigned to clients who have a status <> 2. The current SQL will not find them because of the Status = 2 requirement.

Dealing with that is a bit tricky. It entails counting all the assignments for the sales person and then subtracting those assignments where Status <> 2.

You still need to keep "Status = 2" in the LD query and the SP query becomes

Set SP = db.OpenRecordset( _
"Select * FROM " & _
"(" & _
"SELECT S.ID , (Count(C.StaffAllocated) - " & _
"               (Select Count(C1.StaffAllocated) As SA " & _
"                From Tbl_Staff As S1 INNER JOIN Tbl_Client As C1 " & _
"                     ON S1.ID = C1.StaffAllocated " & _
"               WHERE  S1.ID = S.ID AND C1.Status <> 2 ))  As StaffCount " & _
" FROM Tbl_Staff As S INNER JOIN Tbl_Client As C " & _
"      ON S.ID = C.StaffAllocated " & _
" WHERE S.Available = TRUE " & _
" GROUP BY S.ID " & _
" " & _
" UNION " & _
" " & _
"SELECT S.ID , Count(C.StaffAllocated) As StaffCount " & _
" FROM Tbl_Staff As S LEFT JOIN Tbl_Client As C " & _
"      ON S.ID = C.StaffAllocated " & _ 
" WHERE S.Available = TRUE AND C.StaffAllocated IS NULL " & _
" GROUP BY S.ID  ) As X " & _
" " & _
" ORDER BY 2 "

I know that's a bit gory but take it in steps

First Query Before UNION
Computes all of the assignments that a staff member has and then subtracts those assignments to clients that have Status <> 2.

Second Query After UNION
Picks up those staff members who have no assignments.

UNION
Merges the two above queries together.

ORDER BY 2
Sorts the results from fewest assignments to most assignments.

If you have questions ... please ask.