If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"last" command problem
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!! |
#2
|
|||
|
|||
"last" command problem
"Stan" wrote in message
... 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 ... -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
"last" command problem
Stan wrote:
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 -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
"last" command problem
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 "Stan" wrote: 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!! |
#5
|
|||
|
|||
"last" command problem
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); |
#6
|
|||
|
|||
"last" command problem
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 '================================================= === Stan wrote: 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); |
Thread Tools | |
Display Modes | |
|
|