qry to return only MAX of date value

[Guest]

I am working with a large data set of test results. Each patient may have
multiple test results. I am using a summation query which returns all the
values sorted with the MAX value first but I want to return only the MAX
value of the date and time field for each test, not any values prior. Can
anyone off a solution? Thank you.

 

[Joseph Meehan]

I am working with a large data set of test results. Each patient may
have multiple test results. I am using a summation query which
returns all the values sorted with the MAX value first but I want to
return only the MAX value of the date and time field for each test,
not any values prior. Can anyone off a solution? Thank you.

First question. Is each test result a separate record? In other words
is the data normalized.

Depending on the needs, I can see a patient table a test table and maybe
a results table (test and results may be one table depending on the use)

I am assuming that a given patient may have the same test performed more
than once and may have several results for that test.

 

[Guest]

Yes, Each record holds the acct#, name, date and time of test, and result.

I am thinking there is a way to use a summation query inside a second query
with the MAX of the date and time field in the second or maybe a third
cascaded query but each time I get multiple test results for each patient

 

[sebt]

Hi Jeff

If you just want the maximum (i.e. latest) date and time, for each
patient, then (assuming acct# corresponds to patient) this will do it:

SELECT acct#,Max(datetimeoftest) AS LatestTestData FROM....[table(s)]
GROUP BY acct#

If you want the results of the latest test as well, it gets more
complicated - as you said, you need to use subqueries. I've come
across this one often, and wished there was a "select these columns
from the row where this other column is the MAX" functionality in SQL.
Now I put it that way I've just realised why there isn't: because more
than one row could (theoretically) be identified as the "max" row - if
a patient had two tests at exactly the same time. Not a problem in
this case, I guess.
To get the latest result, try this:

SELECT tests.* FROM
(SELECT acct#,Max(dateandtimeoftest) AS LatestTestDate FROM [test
table(s)]
GROUP BY acct#) latest
INNER JOIN
[test table] tests
ON
latest.acct#=test.acct# AND
latest.LatestTestDate=test.dateandtimeoftest

This should only give multiple results if a patient has had two tests
at exactly the same time, and these two are the latest tests.

Another way of doing it is to join the table to itself, finding all
test dates which have no later test date for the same patient:

SELECT earlier.* FROM
[test table] earlier
LEFT JOIN
[test table] later
ON
earlier.acct#=later.acct# AND
earlier.dateandtime<later.dateandtime
WHERE later.[A non-nullable column] IS NULL

let me know if this helps!

cheers


Seb

 

[Joseph Meehan]

Yes, Each record holds the acct#, name, date and time of test, and
result.

I am thinking there is a way to use a summation query inside a second
query with the MAX of the date and time field in the second or maybe
a third cascaded query but each time I get multiple test results for
each patient

I have not done it, but I suspect you are on the right track. Base one
query on the other and you should be able to get what you want. Of course
I am sure there are other ways, maybe better ways, but sometimes the quick
way is best.

 

[sebt]

Hi Jeff

If you just want the MAX (latest) test date for each patient, a single
query will do it:

SELECT acct#,Max(dateoftest) AS LatestTestDate FROM

GROUP BY
acct#

If you want details of the latest test, you'll need to create two
queries. The first one with the SQL above - save it as, for example,
"QryLatestTestDates"

Then make another query, which looks up the test results only for tests
which are the latest for each patient:

SELECT details.* FROM

details
INNER JOIN
QryLatestTestDAtes latest
ON
details.acct#=latest.acct# AND
details.dateoftest=latest.atestTestDate

cheers



Seb

 

[Guest]

For those who may encounter a similar problem, I found the solution under
Queries at "The Access Web": "Getting a related field from a GroupBy (total)
query.

I had used this before but my memory is failing. Thanks for the assist.