Allocating new clients - condition not working 1

[shaunacol]

Golom and PSV kindly helped in a previous thread to get some code working which automatically allocates clients/leads to a salesperson dependent on the salesperson with the least amount of clients/leads already allocated (and whether that salesperson is avialable). However, I needed to make sure that when the code is counting the number of leads/clients a salesperson already has it only counts ones with a 'status' of ACTIVE (which is stored as number 2). This is so that if a salesperson has hundreds of dead leads associated with them they are not counted - otherwise this salesperson will never come to the top of the list to get new cleints/leads allocated. The code directly below works beautifully but counts all leads - not just the ones with a status of ACTIVE.

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 an available Sales Person with the fewest leads. If there's a tie then
' just arbitrarily pick one.

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)")

' 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


SP.Close
Set SP = Nothing
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




We tried to change the code as follows but then the code allocated the same salesperson to every single new lead

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)")





Any help appreciated!

 

[Golom]

I tried PHV's code for SP. It should work (as far as I can see) but it has problems.

Your UPDATE statement has an error. LD![ClientID] is a text field so it needs quotes around it.

I messed with the code in my test tables and, far as I can determine, this works.

Sub AllocateLeads_Click()

Dim db                         As DAO.Database
Dim LD                         As DAO.Recordset
Dim SP                         As DAO.Recordset
Dim SQL                        As String
Dim strID                      As String
Dim Added                      As Long

On Error GoTo BadNews

Set db = CurrentDb

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

With LD
    If .EOF And .BOF Then
        MsgBox "All clients already have staff allocated" & vbCrLf & _
               "There's nothing to do.", vbInformation, "Nothing Allocated"
        .Close
        Set LD = Nothing
        Set db = Nothing
        Exit Sub
    End If
    
    .MoveLast
    .MoveFirst
    
    Debug.Print .RecordCount; " Clients to be allocated"
End With

Do Until LD.EOF

    ' Find the Sales Person with the fewest leads. If there's a tie then
    ' just arbitrarily pick one.
    SQL = "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 Assigned " & _
            " 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 Assigned " & _
            " 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, 1 "
            
    Set SP = db.OpenRecordset(SQL)
  
    ' 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]

    strID = Replace(SP![ID], "'", "''")
    SQL = "UPDATE Tbl_Client " & _
          " SET StaffAllocated='" & strID & "', " & _
          "     FirstContact='" & strID & "' " & _
          "WHERE ClientID = '" & LD![ClientID] & "'"
    
    db.Execute SQL, dbFailOnError
    
    Debug.Print "Allocated '" & SP![ID] & "' (" & SP![Assigned] & ") to client '" & _
                LD![ClientID] & "' (" & LD![Status] & ")"
    
    Added = Added + 1
    
    LD.MoveNext
    
Loop

MsgBox "Complete - " & Added & " Staff Members Assigned to Clients", _
       vbInformation, "Allocation Complete"

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 again Golum! I love all the error handling and messages in this code! However, it doesnt work at the moment - I am getting 3464 - data type mismatch in criteria expression. Could it be that my available field is a tick box and it doesnt recognise true? Other than that perhaps it is somthing to do with another field being a data type that you are unaware of. I notice you say that ClientID is a text field but it is a number field. So ive listed below what everything is and hope that it helps:

Tbl_Client
PK ClientID (Autonumber)
Status (Number)
StaffAllocated (Number)
FirstContact (Number)

Tbl_Staff
PK ID (Automuber)
Available (Yes/No)

I cantg thanks you enough for your help with this. It is going to be the best peice of code ever

 

[Golom]

Could it be that my available field is a tick box and it doesnt recognise true?

Although Access accepts "Yes" and "No" as valid comparisons for numeric fields, the more common versions are "TRUE" and "FALSE" which I just sort of instinctively use.

This code treats "ClientID", "StaffAllocated" and "FirstContact" as text fields.

You probably need

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

AND

SQL = "UPDATE Tbl_Client " & _
      " SET StaffAllocated = " & SP![ID] & ", " & _
      "     FirstContact   = " & SP![ID] & " " & _
      "WHERE ClientID = " & LD![ClientID]

And get rid of the line [blue]strID = Replace(SP![ID], "'", "''")[/blue]

The SQL for recordset SP doesn't make any assumptions about data types so it should be OK.

 

[shaunacol]

It works!! It works!! Genius. So happy. Thank you so much for all your effort with this one and bearing with me

 

[PHV]

Did you tried it with my proposed version of SP (but the golom's version of LD and the UPDATE) ?

 

[shaunacol]

HI PHV, I cut and paste the code from golom above and then made the suggested changes from Golom. Not sure if your code was in there too somewhere as I have lost track! Thank you to you both, you really helped me a lot.

 

[Golom]

PHV

I tried your [blue]NZ(Status,2) = 2[/blue] version and, although it looks like it should work, it occasionally returned the same record on successive passes through the loop. I couldn't find a pattern to explain why it was doing that. I didn't try the other one.

It would certainly be preferable to have something more concise than that monstrosity that I developed.