find the latest date in access 2007

[roch bell]

hi there!

I'm a bit stressed trying to get this sorted by I just cant!

My question is the following. I have a database of three tables: Dis_cat, admission and patient. The Dis_cate table has only two categories : dead and alive. I just have to find out who was the last patient to die.

I have this code in access:

SELECT Dis_cat.category, Admision.outdate, Patient.pname
FROM Patient INNER JOIN (Admision INNER JOIN Dis_cat ON Admision.dischno = Dis_cat.dischno) ON Patient.nhsno = Admision.nhsno
WHERE (((Dis_cat.category)="dead"));

But I cant find the latest date. I tried using the DMAX but it didnt work. ANY SUGGESTION?

Thanks!

my tables information is:

category outdate pname
Dead 15/12/2009 Gureney
Dead 29/12/2009 Twyman
Dead 22/09/2008 Swaddling
Dead 18/10/2009 Royce
Dead 03/11/2008 Perry
Dead 03/06/2009 Miles
Dead 29/09/2009 Lloyd
Dead 07/01/2010 Kahn
Dead 28/08/2009 Hawkes
Dead 28/10/2009 Frederick
Dead 29/08/2008 Drake
Dead 18/05/2009 Cambridge
Dead 10/01/2009 holloway




Submitted via EggHeadCafe - Software Developer Portal of Choice
C# Threading Http Requests
http://www.eggheadcafe.com/tutorial...69-7010659bd748/c-threading-http-request.aspx

 

[KenSheridan via AccessMonster.com]

Try this:

SELECT category, outdate, pname
FROM Patient INNER JOIN (Admision
INNER JOIN Dis_cat
ON Admision.dischno = Dis_cat.dischno)
ON Patient.nhsno = Admision.nhsno
WHERE outdate =
(SELECT MAX(outdate)
FROM Admision INNER JOIN Dis_cat
ON Admision.dischno = Dis_cat.dischno
WHERE category = "dead");

The subquery returns the latest (MAX) outdate where the category is 'dead'.
This is then used to restrict the outer query to the row or rows where the
outdate equals that date, i.e. the patient or patients who where discharged
dead on the latest discharge date recorded in the database.

Ken Sheridan
Stafford, England

 

[KARL DEWEY]

If your query works otherwise then this should do it for you --
SELECT TOP 1 Dis_cat.category, Admision.outdate, Patient.pname
FROM Patient INNER JOIN (Admision INNER JOIN Dis_cat ON Admision.dischno =
Dis_cat.dischno) ON Patient.nhsno = Admision.nhsno
WHERE (((Dis_cat.category)="dead"))
ORDER BY Admision.outdate DESC;