Query to evenly distribute and assign records

[Linda2431]

Question – I have multiple databases (Front-end, Back-end, Import, Reporting) which enables auditors in our company to audit loans.
I use an Import database which imports a random sample of loans (10%) into the Back End database for audit. I import a spreadsheet of loans (let’s say 5000 loans) into the Import database, and I have the query return 10% of the loans, which is then imported into our Back-end database for audit (500 loans for audit).
Currently the auditors look at the daily list of loans (the 500 imported loans) and divide the work amongst themselves (5 auditors will audit 100 loans each.) They decide Bob will do 1-100; Joe will do 101-200; Bill will do 201-300; etc.

Is there a way using a query to assign these loans to specific auditors when importing the loans into the BE for audit? I would need to enter how many auditors will be working the loans so the work is divided evenly, and then enter the auditor’s initials. (Since the auditors perform different audits, I would like the ability to select or key in auditor initials when importing the loans..

I’ve been wracking my brain but can’t think of a way to do this . . . . . I don’t have a lot of experience programming in Access – so if anyone has any ideas, your feedback is greatly appreciated!!!

 

[michaeljryan78]

you may have to go a bit recursive. my theory is this:

you can use a SELECT TOP 'N' where the 'N' is the number of records divided by the number of auditors. So if 5 auditors and 100 records then top 20 is selected where auditor is null or "". update the auditor and run again for the next N (still the same value = 20) loans and assign to another.

 

[Linda2431]

Thank you! That makes sense to me. Would I need to figure out how many loans to assign to each audior or do you know of a way for Access to tell me? I certainly am able to count and divide before importing, but looking for as much automation as I can . . . . thanks!

 

[michaeljryan78]

You need to modify this for yourself, and I haven't tested, but here is a sample of my thoughts:

Sub test()
Dim strAvailAuditors As String
Dim db As Database
Dim rs1 As Recordset
Dim intAvailTotal As Integer
Dim strLoanAssign As String
Dim intLoanTotal As Integer
Dim intTop As Integer
strAvailAuditors = "Select Auditor From tblAuditors WHere Available = True"
strLoanCount = "Select * FROM tblLoans"
strAssign = "SELECT TOP " & intTop & " LoanNumber, AssignTo FROM tblLoans WHERE AssignTo is NULL"
Set rs2 = db.OpenRecordset(strLoanCount)
    'get the loan count
    rs2.MoveLast
    intLoanTotal = rs2.RecordCount
    rs2.Close
    Set rs2 = Nothing
Set rs1 = db.OpenRecordset(strAvailAuditors)
    'get the list of avail auditors
    rs1.MoveLast
    'get total auditors
    intAvailTotal = rs1.RecordCount
'    get the TOP N records value
    intTop = Round(intLoanTotal / intAvailTotal, 0)
'    move to the first avail auditor
    rs1.MoveFirst
    Do Until rs1.EOF = True
        'loop thru avail auditors
        Set rs2 = db.OpenRecordset(strAsign)
            'open the loans set (TOP N for this auditor) and assign
            Do Until rs2.EOF = True
                'Assigns here
                rs2.Edit
                rs2!assignto = rs1!Auditor.Value
                rs2.Update
                rs2.MoveNext
            Loop
 
        rs2.Close
        Set rs2 = Nothing
        rs1.MoveNext
        'finished with this auditor, now move to the next avail
    Loop
    rs1.Close
    Set rs1 = Nothing
    Set db = Nothing
End Sub

I hope this makes some sense. There may be a bit more elegant solution, but this may be do-able.

 

[Linda2431]

That does make sense to me - I'll do some tweaking tomorrow and see if I can get it to run. Thanks so much!!! I don't have enough Access programming experience to figure this stuff out on my own, but usually am able to modify code once I'm pointed in the right direction. I appreciate your assistance!!!!!

 

[michaeljryan78]

I made an edit. Recheck the post. I forgot the "WHERE AssignTo IS NULL" :banghead:

 

[Linda2431]

OK - I see where you added it -- I appreciate it very much!!! I can't wait to work on it tomorrow ....