Query: Most recent record for unique combination of two fields in MS Access 2010

Question

I'm trying to run a query on some traffic volume data but I'm having some trouble. I would like to pull out the most recent data for each section of road, but this is proving more complicated than expected.

Here is an example of the data I am working with:

S_ID | DATE      | DIRECTION | AADT
1001 | 9/16/2013 | North     | 700
1001 | 9/16/2013 | South     | 600
1001 | 1/30/2011 | NULL      | 1093
1001 | 2/31/2009 | NULL      | 1091
1211 | 1/17/2012 | NULL      | 945
1235 | 1/23/2014 | North     | 500
1235 | 6/19/2014 | South     | 350
1235 | 5/10/2011 | South     | 354
1235 | 5/10/2011 | North     | 399
1301 | 9/16/2013 | East      | 700
1301 | 4/16/2012 | West      | 600
1301 | 1/30/2014 | NULL      | 1093

I am interested in the returning the most recent AADT (Annual Average Daily Traffic) value for each SectionID and direction. In the data above, I would want the values below returned:

S_ID | DATE      | DIRECTION | AADT
1001 | 9/16/2013 | North     | 700
1001 | 9/16/2013 | South     | 600
1211 | 1/17/2012 | NULL      | 945
1235 | 1/23/2014 | North     | 500
1235 | 6/19/2014 | South     | 350
1301 | 1/30/2014 | NULL      | 1093

I have the following code:

SELECT  a.*
INTO    recent_data
FROM    primarytrafficcountdata a
INNER
JOIN    (
        SELECT  section_id,
                direction,
                MAX(date) AS maxdate
        FROM    primarytrafficcountdata
        WHERE   YEAR(date) <= 2014
        AND     YEAR(date) >= 2011
        GROUP BY
                section_id,
                direction
        ) groupeda
ON      (a.section_id = groupeda.section_id
AND      a.direction = groupeda.direction
AND      a.date = groupeda.maxdate)
OR      (a.section_id = groupeda.section_id
AND      a.direction IS NULL and groupeda.direction IS NULL
AND      a.date = groupeda.maxdate)
;

But this returns:

S_ID | DATE      | DIRECTION | AADT
1001 | 9/16/2013 | North     | 700
1001 | 9/16/2013 | South     | 600
1001 | 1/30/2011 | NULL      | 1093
1211 | 1/17/2012 | NULL      | 945
1235 | 1/23/2014 | North     | 500
1235 | 6/19/2014 | South     | 350
1301 | 9/16/2013 | East      | 700
1301 | 4/16/2012 | West      | 600
1301 | 1/30/2014 | NULL      | 1093

This is the most recent dates, which I want, but I want to eliminate the North/South or East/West directions and only use the NULL directions when the NULL direction is more recent or vice-versa when the North/South or East/West are the most recent records.

Does anyone know how I would do this?

I am using Microsoft Access 2010.

Answer 1

Try this:

SELECT  a.* INTO recent_data 
FROM primarytrafficcountdata a 
 INNER JOIN (SELECT section_id,MAX(date) AS maxdate 
              FROM primarytrafficcountdata 
              WHERE YEAR(date) <= 2014 AND YEAR(date) >= 2011 
             GROUP BY section_id) groupeda 
 ON (a.section_id = groupeda.section_id AND a.date = groupeda.maxdate);