Another "First" and "Last" Question

**Ganymede** · 01-27-2012, 08:07 PM

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.

I have an arbitrators table that looks like:

I have a cases table that looks like this:

I have a query that checks the cases table to find the latest cases each arbitrator was assigned to by referencing the "Received" column. It also filters by region, based on the value of the "region" field in my ADR input form. Here's how the query looks in design mode. Notice that I have it set to sort ascending:

And here's how it looks in execution, assuming the value of the region field in my ADR input form is "East".

The effect is that every time I assign an arbitrator to a case, assuming I maintain the round robin rotation, the arbitrator listed at the top of my query will move to the very back, and the arbitrator one slot behind him will progress one forward. The problem is that I don't know how to tell access to automatically assign the arbitrator at the top of the query. I don't know how to reference something by "first record in sorted query".

My first guess was the "first" function, but some research suggested that it would be of no help:

The First(), Last(), DFirst(), and DLast() functions ignore sort orders, indexes, and primary keys. These functions return the first or last undeleted record based on the order in which the records were entered into the table, not the first or last record in a specified sort order.

http://support.microsoft.com/kb/208190

In other words, I can't use the first function to find the value at the top of my query because it doesn't even recognize sort orders. However, someone with a lot of experience made the following comment on the Mr. Excel forum:

The FIRST and LAST functions are two of the most misunderstood functions in Access. It doesn't do what it would appear to do because, as James pointed out, Access does not store data in any particular order. So, even though, when you open a table (which you are really doing is opening a query to the data) and you look at it, it may appear to be in a specific order and if there is a primary key it will attempt to order it by primary key, but the data is not really stored that way.

So, in order to use the first or last functions you need to use a query to sort to a specific field, or fields, and then it will select the first or last record returned by that query. If no order is applied, then it can be a "crap shoot" as to what comes up.

http://www.mrexcel.com/forum/showthread.php?t=400830

According to him, it should recognize sort order. However, I've had no such luck. I've tried referencing the "First" value in two ways:

1. Subquery:

Based on the NextArble table:

But this doesn't work. It returns with the name of an arbitrator in the middle of the pack.

2. Formula in form:

Code: