assign analyst to a case with criteria

[megatronixs]

Hi all,

I need to assign a case to an analyst with a certain criteria.
To avoid that the analyst will just take the easy cases, he will need to press a button that will assign the case to him. The criteria for this is that quality check date is empty ,checker name is empty and the last status date is the oldest date.
I created a make table with the criteria. Then I need to update the main data table, but I got lost how to get this solved. My second thought was to insert the date and name already in the make table, then with the record Id in mind, a update query. But, I simply can't even find it in google.
Maybe my keywords for search don't get results

Any ideas how to get this going?

 

[jdraw]

I'm not getting a clear understanding of the issue.

You have cases, and analysts.
What exactly (in plain English) is the criteria for assigning a case to an available analyst?

Some analysts can only handle certain "types" of Cases?
What is the rationale for not assigning the next Case to the next available Analyst?

Good luck.

 

[megatronixs]

hi Jdraw, sorry for my bad information :-(

All analyst will press the button and then it should assign them the oldest case available. The criteria is: checker field with empty checker name, and empty checker date and the field status date the oldest. so it should get the top 1 of the list.

Maybe this is possible with a update query. will check those fields and then add the name in the checker field and the date in the checked date field.

this is the SQL to create the table I have so far:

SELECT TOP 1 tbl_main_data.record_id, tbl_main_data.uid, 
tbl_main_data.status_date, tbl_main_data.quality_checker, 
tbl_main_data.quality_checking_date, tbl_main_data.quality_pass_fail, 
tbl_main_data.corrected INTO tbl_next_case
FROM tbl_main_data
WHERE (((tbl_main_data.quality_checker) Is Null) AND 
((tbl_main_data.quality_pass_fail) Is Null))
ORDER BY tbl_main_data.status_date;

Maybe instead of creating a table, update the table according the criteria?

Greetings.

 

[The_Doc_Man]

What I don't see is why you bother to create a new table anyway. Just leave ALL of the records in the main table but use WHERE clauses to select the ones that meet your criteria. Moving records to other tables simply means you are doing "double entry bookkeeping" - which is technically a denormalized database. Record-keeping needs to be in a single place so there is never a question as to which record is considered the "definitive" record of what you have been doing. Once you make a copy of the main record, does the copy become the "real record"? Probably not, I would think.

I might do something like this - and I'll use my own nomenclature to simplify the problem. Substitute yours to make it match your needs.

strUPD = "UPDATE tbl_main_data SET " & _
             "assigned_person_id = " & CStr( current_analyst_id) & ", " & _
             "status = 'Assigned', " & _
             "status_date = Now() "& _
<other possible updates go here>
        "WHERE record_id IN " & _
        "(SELECT TOP 1 record_id FROM tbl_main_data WHERE " & _
        "assigned_person_id = 0 ORDER BY status_date ) ;"
DoCMD.RunSQL strUPD

This lets you select the single oldest waiting item (SELECT TOP 1, oldest status_date) that isn't assigned (assigned_person_id = 0) and get the record id of that item to use in selecting the record to update. You assign it to the analyst requesting the assignment (current analyst id).

 

[jdraw]

I agree with Doc --no need for a new table.
I did edit your post to show the SQL within the window.

 

[megatronixs]

hi The_Doc_Man,

I managed to make it work with your code
It is really great and I had really no clue this was possible like that and that is why I was using to create table and then use that to update.

I still have a little thing that I did not manage to fix. It is about showing the record in the form when the user is assigned.
if the record happens to be record_id 6, then the form should display record_id 6.
In my code it sticks to the record_id 6 (I have only 2 records in the test database: record_id 6 and record_id 7).

Please see below code:

Private Sub Command85_Click() ' next case button
    Dim stDocName As String
    Dim sSQL As String
    Dim stLinkCriteria As String
    On Error GoTo errr
    sSQL = "UPDATE tbl_main_data SET " & _
                "quality_checker = GetUserFullName(), " & _
                "quality_checking_date = Date() " & _
                "WHERE record_id IN " & _
                "(SELECT TOP 1 record_id FROM tbl_main_data WHERE " & _
                "(quality_checker) Is Null And (quality_pass_fail) Is Null ORDER BY status_date);"
    DoCmd.RunSQL sSQL
stDocName = "frm_main_data"
stLinkCriteria = "[record_id]=" & [record_id]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
  Me.frm_my_cases_sub.Requery
        Me.Requery
    Me.Refresh
Exit Sub
errr:
    MsgBox Err.Description
End Sub

Greetings.

 

[megatronixs]

hi all,

I managed to solve it

Private Sub Command85_Click() ' next case button
    Dim stDocName As String
    Dim sSQL As String
    Dim sSQL2 As String
    Dim stLinkCriteria As String
    Dim Das_Record_id As String
    Dim rst As DAO.Recordset
    
    Dim longRecordId As String
    
    longRecordId = "SELECT TOP 1 record_id FROM tbl_main_data WHERE " & _
                "(quality_checker) Is Null And (quality_pass_fail) Is Null ORDER BY status_date, uid;"
                
                Set rst = CurrentDb.OpenRecordset(longRecordId)
                Das_Record_id = rst!record_id
    
 Debug.Print Das_Record_id
    
    On Error GoTo errr
    sSQL = "UPDATE tbl_main_data SET " & _
                "quality_checker = GetUserFullName(), " & _
                "quality_checking_date = Date() " & _
                "WHERE record_id IN " & _
                "(SELECT TOP 1 record_id FROM tbl_main_data WHERE " & _
                "(quality_checker) Is Null And (quality_pass_fail) Is Null ORDER BY status_date, uid);"
    DoCmd.RunSQL sSQL
stDocName = "frm_main_data"
stLinkCriteria = "[record_id]=" & [Das_Record_id]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
  Me.frm_my_cases_sub.Requery
        Me.Requery
    Me.Refresh
Exit Sub
errr:
    MsgBox Err.Description
End Sub

Greetings.