query with Max(Date)

[Mario Krsnic]

Hello everybody,
I have the query:

SELECT personen.Familienname, personen.Vorname, Diagnose, Evaldatum,
perspflege2.lfd_nr
FROM (perspflege2 INNER JOIN personen ON personen.lfd_nr=perspflege2.lfd_nr)
INNER JOIN diageval ON diageval.id=perspflege2.id
WHERE last(diageval.myDate) = 2/2/2008;

The Access tells me, this is not possible. How to make the query with the
last (greatest) Date from the tabel DiagEval.
Thank you for your help.
Mario

 

[John Spencer]

Perhaps the following is what you want.


SELECT personen.Familienname
, personen.Vorname
, Diagnose
, Evaldatum
, perspflege2.lfd_nr
FROM (perspflege2 INNER JOIN personen ON
personen.lfd_nr=perspflege2.lfd_nr) INNER JOIN diageval
ON diageval.id=perspflege2.id
GROUP BY personen.Familienname
, personen.Vorname
, Diagnose
, Evaldatum
, perspflege2.lfd_nr
HAVING MAX(Diageval.myDate) = #2008-02-02#


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Mario Krsnic]

Yes, John, thank you!

Perhaps the following is what you want.


SELECT personen.Familienname
, personen.Vorname
, Diagnose
, Evaldatum
, perspflege2.lfd_nr
FROM (perspflege2 INNER JOIN personen ON
personen.lfd_nr=perspflege2.lfd_nr) INNER JOIN diageval
ON diageval.id=perspflege2.id
GROUP BY personen.Familienname
, personen.Vorname
, Diagnose
, Evaldatum
, perspflege2.lfd_nr
HAVING MAX(Diageval.myDate) = #2008-02-02#


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================