"last" command problem

[Guest]

Hi. I have a database that keeps patient followup information for outcomes
after treatment for cancer.

One of my tables has multiple rows of data (toxicity followup) per person
for distinct dates (clinic visits). I am trying to build a query in design
view that requests specific followup data for the last followup date only.
The output of this query would therefore be one row per patient with the
selected toxicity data at last followup.

1) I am able to identify the last date of followup by viewing totals and
grouping by "max," but not "last." I don't understand why they would not
return the same value.

2) I can get the last followup date using "max", but i am stuck getting the
associated data for that max date. Is there a way to get data for that last
date in the design view?

One idea i had was to make an intermediary query that sorts these data by
date, and then use the "last" command to get the last row of data, but this
does not return the correct last value.

any help would be greatly appreciated. thanks!!

 

[Rick Brandt]

Hi. I have a database that keeps patient followup information for outcomes
after treatment for cancer.

One of my tables has multiple rows of data (toxicity followup) per person
for distinct dates (clinic visits). I am trying to build a query in design
view that requests specific followup data for the last followup date only.
The output of this query would therefore be one row per patient with the
selected toxicity data at last followup.

1) I am able to identify the last date of followup by viewing totals and
grouping by "max," but not "last." I don't understand why they would not
return the same value.

2) I can get the last followup date using "max", but i am stuck getting the
associated data for that max date. Is there a way to get data for that last
date in the design view?

One idea i had was to make an intermediary query that sorts these data by
date, and then use the "last" command to get the last row of data, but this
does not return the correct last value.

any help would be greatly appreciated. thanks!!

Use Max() and Min(). Last() and First() are (for the most part) completely
useless. First is handy when you don't care. Last is handy when ...

 

[Marshall Barton]

Hi. I have a database that keeps patient followup information for outcomes
after treatment for cancer.

One of my tables has multiple rows of data (toxicity followup) per person
for distinct dates (clinic visits). I am trying to build a query in design
view that requests specific followup data for the last followup date only.
The output of this query would therefore be one row per patient with the
selected toxicity data at last followup.

1) I am able to identify the last date of followup by viewing totals and
grouping by "max," but not "last." I don't understand why they would not
return the same value.

2) I can get the last followup date using "max", but i am stuck getting the
associated data for that max date. Is there a way to get data for that last
date in the design view?

One idea i had was to make an intermediary query that sorts these data by
date, and then use the "last" command to get the last row of data, but this
does not return the correct last value.

An alternative approach would be to use:

SELECT TOP 1 *
FROM table
WHERE patient = . . .
ORDER BY visitdate DESC

 

[Guest]

To do this entirely in design view you'd need to use two queries.

First create a query which groups the rows by patient and returns the MAX
visit date. Return just those two columns. I SQL this would look like
something like this:

SELECT PatientID, MAX(VisitDate) As LatestDate
FROM YourTable
GROUP BY PatientID;

Then join the original table to this query on the PatientID columns and on
the date columns. In SQL it would look something like this:

SELECT YourTable.*
FROM YourTable INNER JOIN YourQuery
ON YourTable.PatientID = YourQuery.PatientID
AND YourTable.VisitDate = YourQuery.LatestDate;

You can do the whole thing in one query, however, by means of a subquery:

SELECT *
FROM YourTable AS T1
WHERE VisitDate =
(SELECT MAX(VisitDate)
FROM YourTable AS T2
WHERE T2.PatientID = T1.PatientID);

Note how the aliases T1 and T2 are used here to distinguish the two
instances of the table.

Ken Sheridan
Stafford, England

 

[Guest]

thanks all. Ken i used the design view to get your inner join recommendation
to work. the TOP argument will output one patient row of data at a time but i
would need all patients. i want to learn how to write the subquery correctly
in sql but i can't quite get the sql to work - i get "enter parameter value"
at the start of the query.
any idea what's wrong with:
SELECT T1.*
FROM [late tox] AS T1
WHERE [late tox].date=
( SELECT Max([late tox].date)
FROM [late tox] AS T2
WHERE T1.patientID = T2.patientID);

 

[John Spencer]

When you "alias" a table you must then use that name whenever you want
to refer to that instance of the table. So try

SELECT T1.*
FROM [late tox] AS T1
WHERE [T1].date=
( SELECT Max([T2].date)
FROM [late tox] AS T2
WHERE T1.patientID = T2.patientID);

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================