Don't know how to describe...

[Ganymede]

I run an ADR Panel with ~20 arbitrators. I get a lot of cases. I'm expected to circulate the work between the arbitrators in a round-robin system. However, I'm also supposed to assign the arbitrators depending on what region the case is based out of: East, Middle and West.



My arbitration table looks like this (simplified for example), but on a split form so I can add and remove arbitrators easily:



My cases table looks like this (also simplified), except its on a split form so I can add and remove cases easily:



Basically, I want to design the database to automatically designate the next arbitrator in line whenever I create a new case record (obviously after I select the region for that case).

Here's what I've attempted:

The "arbitrator" field in the form I use to add new case records is a combo box with 2 columns. The first is the arbitrator key. The second is the arbitrator name. I have it set-up so only the name appears, but the key column is the bound column.

I created a text-box on the form I use to add a case. The control source is:

=DLast("[Arbitrator]","ADR","Region=[Region]")

This searches the cases table and returns the key number of the last arbitrator I assigned to a case for that particular region. What I need to do is somehow tell access to find the next arbitrator on the arbitrators table that has the same region. Obviously, there will be cases where the next arbitrator requires going back to the start of the list. I'm wondering "Is access smart enough to have something like this?" Might this be something I have to program on VBA?

[aytee111]

Query: Arbitrator, Region (match to case), Date Assigned sorted ascending
The Arbitrator you need to assign will be the first record returned in the query.

[Ganymede]

Query: Arbitrator, Region (match to case), Date Assigned sorted ascending
The Arbitrator you need to assign will be the first record returned in the query.

Brilliant. Thank you.

The only problem is that I sometimes assign the same arbitrator twice in a day. In fact, I once got 200+ case files in a single day. (Sorry I didn't mention that before.) Thus, I need something more specific than date. But this isn't a problem. I'll just create a new field for the Now() function in my cases table so I can capture date and time. Thus, your idea still works. Thank you.