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?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
allocating new clients based on the least number 1
- Thread starter shaunacol
- Start date
- Thread starter
- #21
Thanks, you are very thorough! In theory there should never be a case where we need to allocate a salesperson to a lead which is active. The process begins with the import of leads from several sources. At this point the clients/leads have a status of 'new'. Now all these leads need someone allocated which is where this process comes in which you ate kindly helping with. Once they are allocated the salesperson picks them up and the status changes to active once contact is made. I hope this makes sense? Do you still see issues with how I am trying to do it? I just want to make sure that when we count the current number of clients/leads that are allocated to a salesperson that we are counting the active ones and not ones that are dead or closed or inactive for any other reason. Otherwise we could end up never allocating any leads to long terms members of staff who have hundreds if closed leads against their name.
It sounds like there are more ramifications to (I assume) the Status field that have relevance here.
You are using words (e.g. New, Active, Dead, etc.) to describe the client's Status. I assume that those correspond to some numeric values since we are are coding "Status = 2" or "Status <> 2" in the SQL.
Making the assumption that "Status = 2" equates to "active" then the SQL I provided should work. In the LD query however, you will need to change "Status = 2" to "Status = [red]??[/red]" where [red]??[/red] corresponds to the numeric value that means "New" since it is the "New" clients to whom we want to assign staff.
You are using words (e.g. New, Active, Dead, etc.) to describe the client's Status. I assume that those correspond to some numeric values since we are are coding "Status = 2" or "Status <> 2" in the SQL.
Making the assumption that "Status = 2" equates to "active" then the SQL I provided should work. In the LD query however, you will need to change "Status = 2" to "Status = [red]??[/red]" where [red]??[/red] corresponds to the numeric value that means "New" since it is the "New" clients to whom we want to assign staff.
- Thread starter
- #23
Sorry I think you have misinterpretted. I want to allocate a salesperson to all records which dont have a salesperson allocated. The status shouldnt matter. The status matters in the second third of the code where we are counting the number of leads. This is where I want to count only the 'active' (or number 2 as you say) leads. If I count all the leads allocated to a salesperson then I will be counting dead leads and therefore if a salesperson has only a bunch of dead leads they will still never come to the top of the pile to be allocated the next lead. Does this make sense? I am so close thanks to your help. I am getting the error '3122 - you are trying to execute a query thatdosnt include the aggregate function status as part of the expression'. I know its to do with my syntax again and have highlighted where I think the issue is. I am sure this is where my issue is as this code works perfectly until I try to add in the status thing. An I thought it would be so easy to add later 
Private Sub AllocateLeads_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 S.ID[highlight #EDD400], c.status " & _[/highlight]" FROM Tbl_Staff As S LEFT JOIN Tbl_Client As C" & _
" ON S.ID=C.StaffAllocated" & _
" WHERE S.Available=yes [highlight #FCE94F]AND c.status = 2" & _[/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]
Dim strID As String
strID = Replace(SP![ID], "'", "''")
db.Execute "UPDATE Tbl_Client" & _
" SET StaffAllocated='" & strID & "'" & _
",FirstContact='" & strID & "'" & _
"WHERE ClientID = " & LD![ClientID] & ";", dbFailOnError
LD.MoveNext
Loop
Exit_Sub:
MsgBox "All new leads have been allocated"
Set LD = Nothing
Set SP = Nothing
Set db = Nothing
Exit Sub
BadNews:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Sub:
End Sub
Private Sub AllocateLeads_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 S.ID[highlight #EDD400], c.status " & _[/highlight]" FROM Tbl_Staff As S LEFT JOIN Tbl_Client As C" & _
" ON S.ID=C.StaffAllocated" & _
" WHERE S.Available=yes [highlight #FCE94F]AND c.status = 2" & _[/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]
Dim strID As String
strID = Replace(SP![ID], "'", "''")
db.Execute "UPDATE Tbl_Client" & _
" SET StaffAllocated='" & strID & "'" & _
",FirstContact='" & strID & "'" & _
"WHERE ClientID = " & LD![ClientID] & ";", dbFailOnError
LD.MoveNext
Loop
Exit_Sub:
MsgBox "All new leads have been allocated"
Set LD = Nothing
Set SP = Nothing
Set db = Nothing
Exit Sub
BadNews:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Sub:
End Sub
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 AND Nz(c.status,2)=2" & _
" GROUP BY S.ID" & _
" ORDER BY Count(C.StaffAllocated)")
Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
"SELECT S.ID" & _
" FROM Tbl_Staff As S LEFT JOIN Tbl_Client As C" & _
" ON S.ID=C.StaffAllocated" & _
" WHERE S.Available=yes AND Nz(c.status,2)=2" & _
" GROUP BY S.ID" & _
" ORDER BY Count(C.StaffAllocated)")
Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
- Thread starter
- #25
- Thread starter
- #26
Sorry PHV, ignore my last post as I had not seen the post from 5 mins before that - I was responding to your post earlier today.
As far as I can see the last post from PSV works!!!! Wow amazing. I dont really understand how but it seems to work beautifully! Huge thanks to PSV and to Golom. You are fantastic!
As far as I can see the last post from PSV works!!!! Wow amazing. I dont really understand how but it seems to work beautifully! Huge thanks to PSV and to Golom. You are fantastic!
- Thread starter
- #27
Just realised that the code only picks up staff who have some 'active' leads already so if a member of staff has closed all their leads and is awaiting a new lead they will get ignored. If no staff have any active leads I get error "3021 - no currrent record"
Going to look into this tomorrow. I guess I somehow need to say count the number of active leads but please also include anyone who is listed as available into the mix.
Going to look into this tomorrow. I guess I somehow need to say count the number of active leads but please also include anyone who is listed as available into the mix.
And this ?
Set SP = db.OpenRecordset( _
"SELECT S.ID FROM Tbl_Staff As S LEFT JOIN" & _
" (SELECT StaffAllocated FROM Tbl_Client WHERE status=2) As C" & _
" ON S.ID=C.StaffAllocated" & _
" WHERE S.Available=yes" & _
" GROUP BY S.ID" & _
" ORDER BY Count(C.StaffAllocated)")
Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
Set SP = db.OpenRecordset( _
"SELECT S.ID FROM Tbl_Staff As S LEFT JOIN" & _
" (SELECT StaffAllocated FROM Tbl_Client WHERE status=2) As C" & _
" ON S.ID=C.StaffAllocated" & _
" WHERE S.Available=yes" & _
" GROUP BY S.ID" & _
" ORDER BY Count(C.StaffAllocated)")
Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
- Thread starter
- #29
Thanks for response. i dont get the error anymore but it allocates the same person to all 12 unallocated leads. Seems to be the last person without any active leads that gets them all. Would it make any difference to get rid of the staff allocated from that part of the code and adding it into the final part of the code that actually allocated the staff (i.e. allocate only staff who are available)? Will think more about this.
- Thread starter
- #31
- Thread starter
- #32
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question