I have a Microsoft Access table in which I want to return a set of records, but only for the most recent date. 

My data has 24 records with 12 of these have RECORD\_DATE=#23/04/2020#, the other 12 have RECORD\_DATE=#24/04/2020# (ie. 12 with 23-April, 12 with 24-April); the field is set as Date/Time.

I have written a subquery to get the Max date for each record.

The problem I have is that my query executes, but returns all the records in the table (well, it applies the "WHERE Q.SITE\_ID=?" correctly), not just those with the MAX(RECORD\_DATE). 

Here is the SQL:

```
SELECT 
    Q.CONSUMABLE_RECORD_ID,
    Q.SITE_ID,
    Q.CONSUMABLE_STORAGE_ID,
    Q.CONSUMABLE_PRODUCT_ID,
    Q.CONSUMABLE_MEASUREMENT_UNIT_ID,
    Q.RECORD_DATE,
    Q.RECORD_VALUE,
    Q.LAST_DATE,
    Q.LAST_VALUE 
FROM 
    CONSUMABLE_RECORD AS Q 
WHERE 
    Q.SITE_ID =? 
AND 
    Q.RECORD_DATE=
    (
    SELECT 
        MAX(S.RECORD_DATE) 
    FROM 
        CONSUMABLE_RECORD AS S 
    WHERE 
        Q.CONSUMABLE_RECORD_ID=S.CONSUMABLE_RECORD_ID
    )

```

If it makes any difference, CONSUMABLE\_RECORD\_ID is the Primary Key, and I am executing the query using and OleDbCommand via C#, and the data provider I am using is Microsoft.ACE.OLEDB.16.0. I've also tried using MAX(CONSUMABLE\_RECORD\_ID) in the subquery, but that didn't work - I'd prefer to keep it as Max(RECORD\_DATE) as theoretically records could be entered out-of-sequence on date.

What do I need to do to get this working? I tried 'TOP 1' in the subquery, and still get the 24 rows back. 

I would prefer to keep this as a subquery rather than an Inner Join etc.

Edit:  

This is different from [This answer](https://stackoverflow.com/questions/41220690). In that one, they are looking for the top 3 results in a group - I just want the maximum record. I tried adding TOP 1 to the start of the question, and and ORDER BY (which necessitates a GROUP BY), but I'm still getting all the records back.

Edit 2:
Sample Data:
[![Sample data and structure](https://i.stack.imgur.com/dkCAM.png)](https://i.stack.imgur.com/dkCAM.png)

The desired result is just all the records with RECORD\_DATE=#24/4/20#.