How do I get the Max Date

[Guest]

I have a query that has the following fields:

PatientID
CaseNo
CaseBgnDt
CaseEndDt
RN
LogFrmDt
LogThruDt
LogType

There can be more than 1 log in the table and I only want to pull the most
recent Log. How would I do this? I'm assuming I will use the LogFrmDt but I
cannot get my query to work using the Max function.

Thanks.

 

[Michel Walsh]

You can use one of the four method presented at
http://www.mvps.org/access/queries/qry0020.htm


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

hi,

you could use a function... Max() in particular, as in the following example:

SELECT tbl_name.PatientID, Max(tbl_name.LogFrmDt ) AS last_LogFrmDt
FROM tbl_name
GROUP BY tbl_name.PatientID;

To the preceding, you can add your WHERE clause limiting output records as
desired.

hope this helps,
geebee

 

[Michel Walsh]

It does not supply the OTHER fields of the row associated to the MAX, for
each patientID. If ONLY the max value is required, that is fine. As example,
if you have a library, you may want WHEN, for the last time, each book has
been out: SELECT bookID, MAX(outDate) FROM... would do, but if, in
addition, you want also know "by who", you need the associated fields to
that max.


Vanderghast, Access MVP