SubQueries in MS ACCESS: selecting only one record per "person" per date.

Question

I am using a table called analyzed in Microsoft Access. It has many fields but the three ones being used for filtering in this case are analyzed.readings_miu_id, analyzed.ReadDate, analyzed.ReadTime. I need to pull the records from the "analyzed" table where readings_miu_id are grouped together then sorted by ReadDate but showing only the last entry for said date which could be the highest valued time in ReadTime. I've created a query:

SELECT readings_miu_id, Reading, ReadDate, ReadTime, 
    MIUwindow, SN, Noise, RSSI, OriginCol, ColID, Ownage
FROM analyzed
WHERE analyzed.ReadDate Between #4/21/2009# and #4/29/2009#  
AND analyzed.ReadTime= ( 
    SELECT TOP 1 analyzed.ReadTime 
    FROM analyzed 
    WHERE analyzed.readings_miu_id = *????*** 
    ORDER BY analyzed.ReadTime DESC);

*????*** is being used to show that im not sure what to put here

if i enter in a valid readings_miu_id i get one record with the highest time for that readings_miu_id.

How do i use a sub query like the one above to pull only the last ReadTime per readings_miu_id per ReadDate?

*readings_miu_id* is not the table ID, it is more similar to an Item Number or a Name

Answer 1

this will get you close. The statement "where readings_miu_id are grouped together" is confusing me. not sure if you want a GroupBy or a sort on that field. This query will give youthe records from your analyzed table that have the same date, but the "biggest:" time for that given date. Sounds like it's what you wanted.

SELECT readings_miu_id, Reading, ReadDate, ReadTime, MIUwindow, SN, Noise, RSSI, OriginCol, ColID, Ownage 
FROM analyzed ,
(
  Select ReadDate as matchDate, Max(ReadTime) as matchTime
  FROM analyzed 
  Where analyzed.ReadDate Between #4/21/2009# and #4/29/2009# 
  Group by ReadDate
) dateAndTime
WHERE analyzed.ReadDate Between #4/21/2009# and #4/29/2009# AND ReadDate = dateAndTime.matchDate AND readTime = dateAndTime.matchTime  
Order By readDate

Answer 2

 SELECT readings_miu_id
    , Reading , ReadDate , ReadTime
    , MIUwindow, SN, Noise, RSSI
    , OriginCol, ColID, Ownage 
 FROM analyzed AS A
    WHERE analyzed.ReadDate Between #4/21/2009# and #4/29/2009# 
       AND analyzed.ReadTime=
          (SELECT TOP 1 analyzed.ReadTime FROM analyzed 
             where analyzed.readings_miu_id = A.readings_miu_id 
                AND analyzed.ReadDate = A.ReadDate
             ORDER BY analyzed.ReadTime DESC);

I just aliased the main table as A and just refered to it in the subquery. Not sure if you need to filter by ReadDate in your subquery.