query with Max(Date)

  • Thread starter Thread starter Mario Krsnic
  • Start date Start date
M

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
 
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
'====================================================
 
Yes, John, thank you!

John Spencer said:
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
'====================================================
Click to expand...
 
Back
Top