ms access - Getting the last date from a set of records -

********edited*********

basically looking last appointment dog in our shop details of appointment. know, right pulling max date, have customers booked out entire year, not getting last appointment in, last appointment booked in future.

select dbo_pet.petid,           dbo_pet.petname,           dbo_customer.cstlname,           dbo_vwapptgrid.aptdate,           dbo_vwapptgrid.aptid,           dbo_vwapptgrid.groomstyle,           dbo_vwapptgrid.statusdescr (dbo_customer right join dbo_pet on dbo_customer.[cstid] = dbo_pet.[petcustid])           left join dbo_vwapptgrid on dbo_pet.petid = dbo_vwapptgrid.aptpetid (((dbo_pet.petid)=[id])           , ((dbo_vwapptgrid.aptdate)<=date())           , ((dbo_vwapptgrid.aptdate)=(select max(tmax.aptdate)  dbo_vwapptgrid tmax  tmax.aptpetid = dbo_pet.petid))); 

shows no records @ all. if change max date greater today shows last booked appointment, should. why not limiting max date less today??

there multiple ways go this, prefer filtering totals subquery (feels descriptive want me).

the solution commented @june7 valid (especially if don't need fields he/she referred to).

when adding additional criteria, add these subquery, never main query. if add them main query, won't results, since subquery return filtered out.

select dbo_pet.petname     ,dbo_customer.cstlname     ,dbo_vwapptgrid.aptid     ,dbo_vwapptgrid.aptdate most_recent     ,dbo_vwapptgrid.groomstyle     ,dbo_vwapptgrid.statusdescr (     dbo_customer right join dbo_pet on dbo_customer.[cstid] = dbo_pet.[petcustid]     ) left join dbo_vwapptgrid on dbo_pet.petid = dbo_vwapptgrid.aptpetid  dbo_pet.petid = [id]  , dbo_vwapptgrid.aptdate = (select max(tmax.aptdate) dbo_vwapptgrid tmax tmax.aptpetid = dbo_pet.petid , tmax.aptdate <= date());