Access: grouping according to most recent date in a record (nested query)

Question

This Query on the table below

SELECT ID, 
       Value, 
       As_of 
FROM Table a 
INNER JOIN (
    SELECT ID, 
           MAX(As_of) AS As_of 
    FROM Table 
    GROUP BY ID
) b 
ON a.ID=b.ID 
AND a.As_of = b.As_of

Table:

ID      Value  As_of
1173    156    20090601
1173    173    20081201
1173    307    20080901
1173    305    20080601
127     209    20090301
127     103    20081201
127     113    20080901
127     113    20080601
1271    166    20090201
1271    172    20081201
1271    170    20080901
1271    180    20080601

Makes it look like this

Result
ID      Value    As_of
1173    156      20090601
127     209      20090301
1271    166      20090201

It gets the MAX for the As_of for each ID and returns that... (from another Question on stackoverflow)

I have basically the same table but not as a "physical table" but as a query result:

Query:

SELECT tblClassificationHistory.inmateID, 
       tblClassificationHistory.classificationID
FROM tblinmate 
INNER JOIN tblClassificationHistory 
ON tblinmate.inmateID = tblClassificationHistory.inmateID
GROUP BY tblClassificationHistory.inmateID, 
         tblClassificationHistory.classificationID,                
         tblClassificationHistory.reclassificationDate
ORDER BY tblClassificationHistory.inmateID;

Makes this Result:

inmateID    classificationID    reclassificationDate
2              3                       9/22/2015
2              4                       9/13/2015
2              8                       9/8/2015
3              8                       9/13/2015
4              4                       9/15/2015
4              8                       6/16/2015
5              3                       9/15/2015
5              4                       9/16/2015
5              8                       7/7/2015
6              8                       9/14/2015
7              7                       9/13/2015
8              3                       8/4/2015
8              8                       6/1/2015
9              3                       9/15/2015
10              6                       9/13/2015
11              3                       9/13/2015
12              3                       8/3/2015
12              4                       9/9/2015
12              6                       9/13/2015
12              8                       7/6/2015
13              8                       9/13/2015
14              8                       9/13/2015
15              4                       9/13/2015
16              4                       9/13/2015
17              8                       9/14/2015
18              8                       9/14/2015
19              3                       9/15/2015

I want to use "His" query in mine but after a few hours i cant...i presume its a subquery..but i cant for the life of me get the syntax right...

Result Im after: classificationID for Last date for each inmateID

inmateID    classificationID    reclassificationDate
2           3                   9/22/2015
3           8                   9/13/2015
4           4                   9/15/2015
5           4                   9/16/2015
6           8                   9/14/2015
7           7                   9/13/2015
8           3                   8/4/2015
9           3                   9/15/2015
10          6                   9/13/2015
11          3                   9/13/2015
12          6                   9/13/2015
13          8                   9/13/2015
14          8                   9/13/2015
15          4                   9/13/2015
16          4                   9/13/2015
17          8                   9/14/2015
18          8                   9/14/2015
19          3                   9/15/2015

Answer 1

SELECT a.inmateID,
       a.classificationID,
       b.max_date
FROM (
    SELECT tblClassificationHistory.inmateID, 
           tblClassificationHistory.classificationID,                
           tblClassificationHistory.reclassificationDate
    FROM tblinmate
    INNER JOIN tblClassificationHistory
    ON tblinmate.inmateID = tblClassificationHistory.inmateID 
) a
INNER JOIN (
    SELECT tblClassificationHistory.inmateID, 
           MAX(tblClassificationHistory.reclassificationDate) as max_date
    FROM tblinmate
    INNER JOIN tblClassificationHistory
    ON tblinmate.inmateID = tblClassificationHistory.inmateID
    GROUP BY tblClassificationHistory.inmateID  
) b
ON a.inmateID = b.inmateID
AND a.reclassificationDate = b.max_date
ORDER BY a.inmateID;

You can break it down into two subqueries (a and b). In b, we'll find the MAX date in our table, excluding classificationID and grouping only by inmateID. We'll wind up with a table like this:

+----------+-----------+
| inmateID | max_date  |
+----------+-----------+
| 2        | 9/22/2015 |
| 3        | 9/13/2015 |
| 4        | 9/15/2015 |
| 5        | 9/16/2015 |
| 6        | 9/14/2015 |
| 7        | 9/13/2015 |
| ...      | ...       |
+----------+-----------+

In a, we'll simply grab the unaggregated values, including classificationID this go-around:

+----------+------------------+----------------------+
| inmateID | classificationID | reclassificationDate |
+----------+------------------+----------------------+
| 2        | 3                | 9/22/2015            |
| 2        | 4                | 9/13/2015            |
| 2        | 8                | 9/15/2015            |
| 3        | 8                | 9/16/2015            |
| 4        | 4                | 9/14/2015            |
| 4        | 8                | 9/13/2015            |
| ...      | ...              | ...                  |
+----------+------------------+----------------------+

We join the two subqueries, finding rows in a where reclassificationDate is equal to the MAX date with just calculated in b. We can then grab the appropriate classificationID from those joined rows to build our final table.