text: Q:
  
  Why is my Access Max() subquery not working?
  
  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.  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:
  
  The desired result is just all the records with RECORD_DATE=#24/4/20#.
  
  A:
  
  It would seem that you don't want a correlated subquery.  This seems like a concise way of writing what you want:
  SELECT cr.*
  FROM Consumable_Record as cr
  WHERE cr.Site_ID = ? AND
        cr.Record_Date = (SELECT MAX(cr2.Record_Date)
                          FROM Consumable_Record as cr2
                         );
  
  Note the use of meaningful table aliases.  Q and S don't mean anything with respect to your data.  CR is an abbreviation for the table name.
  The above will not return records if the site doesn't have any on that day.  If you want the most recent records per site, then use that in the correlation clause:
  SELECT cr.*
  FROM Consumable_Record as cr
  WHERE cr.Site_ID = ? AND
        cr.Record_Date = (SELECT MAX(cr2.Record_Date)
                          FROM Consumable_Record as cr2
                          WHERE cr2.Site_Id = cr.Site_Id
                         );
  
  I suspect this is what you really want.
  
  