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
|
|||
|
|||
Min value & corresponding date
I need to get the min value of FVC% and the corresponding date that goes
along with this value. I've had no luck just trying to use min from the totals option. This is what I have. From table (tPFT) I need to following fields ingPtID, STUDY_DATE, FVC% ingPtID and STUDY_DATE are both primary keys if that makes a difference. |
#2
|
|||
|
|||
Min value & corresponding date
woods1119 -
This will pull all records where the FVC% is the min(FVC%) in the table: SELECT ingPtID, STUDY_DATE, FVC% FROM tPFT WHERE FVC% = (SELECT min(FVC%) FROM tPFT); -- Daryl S "woods1119" wrote: I need to get the min value of FVC% and the corresponding date that goes along with this value. I've had no luck just trying to use min from the totals option. This is what I have. From table (tPFT) I need to following fields ingPtID, STUDY_DATE, FVC% ingPtID and STUDY_DATE are both primary keys if that makes a difference. |
#3
|
|||
|
|||
Min value & corresponding date
On Wed, 28 Apr 2010 07:10:10 -0700, woods1119
wrote: The general query is like this: select * from myTable where myField = (select Min(myField) from myTable) -Tom. Microsoft Access MVP I need to get the min value of FVC% and the corresponding date that goes along with this value. I've had no luck just trying to use min from the totals option. This is what I have. From table (tPFT) I need to following fields ingPtID, STUDY_DATE, FVC% ingPtID and STUDY_DATE are both primary keys if that makes a difference. |
#4
|
|||
|
|||
Min value & corresponding date
Thanks!
It is recognizing the '%' as a syntax error though. I imagine that whoever developed the database poorly chose to use the % in naming...? "Daryl S" wrote: woods1119 - This will pull all records where the FVC% is the min(FVC%) in the table: SELECT ingPtID, STUDY_DATE, FVC% FROM tPFT WHERE FVC% = (SELECT min(FVC%) FROM tPFT); -- Daryl S "woods1119" wrote: I need to get the min value of FVC% and the corresponding date that goes along with this value. I've had no luck just trying to use min from the totals option. This is what I have. From table (tPFT) I need to following fields ingPtID, STUDY_DATE, FVC% ingPtID and STUDY_DATE are both primary keys if that makes a difference. |
#5
|
|||
|
|||
Min value & corresponding date
woods1119 wrote:
I need to get the min value of FVC% and the corresponding date that goes along with this value. I've had no luck just trying to use min from the totals option. This is what I have. From table (tPFT) I need to following fields ingPtID, STUDY_DATE, FVC% ingPtID and STUDY_DATE are both primary keys if that makes a difference. Does this do what you want" SELECT TOP 1 ingPtID, STUDY_DATE, [FVC%] FROM table ORDER BY [FVC%] -- Marsh MVP [MS Access] |
#6
|
|||
|
|||
Min value & corresponding date
Put all table names, field names, control names, etc. that have a special
character (including spaces) in square brackets. [FVC%] instead of FVC%. -- Daryl S "woods1119" wrote: Thanks! It is recognizing the '%' as a syntax error though. I imagine that whoever developed the database poorly chose to use the % in naming...? "Daryl S" wrote: woods1119 - This will pull all records where the FVC% is the min(FVC%) in the table: SELECT ingPtID, STUDY_DATE, FVC% FROM tPFT WHERE FVC% = (SELECT min(FVC%) FROM tPFT); -- Daryl S "woods1119" wrote: I need to get the min value of FVC% and the corresponding date that goes along with this value. I've had no luck just trying to use min from the totals option. This is what I have. From table (tPFT) I need to following fields ingPtID, STUDY_DATE, FVC% ingPtID and STUDY_DATE are both primary keys if that makes a difference. |
#7
|
|||
|
|||
Min value & corresponding date
Unfortunately none of these are working.
There are about 2500 unique ingPtID in the database. Each of these ingPtID's will have a STUDY_DATE and FVC%. I would like to get the minimum FVC% & corresponding date for each ingPTID. I'm hoping that further info might help. Thanks! "Marshall Barton" wrote: woods1119 wrote: I need to get the min value of FVC% and the corresponding date that goes along with this value. I've had no luck just trying to use min from the totals option. This is what I have. From table (tPFT) I need to following fields ingPtID, STUDY_DATE, FVC% ingPtID and STUDY_DATE are both primary keys if that makes a difference. Does this do what you want" SELECT TOP 1 ingPtID, STUDY_DATE, [FVC%] FROM table ORDER BY [FVC%] -- Marsh MVP [MS Access] . |
#8
|
|||
|
|||
Min value & corresponding date
woods1119 wrote:
Unfortunately none of these are working. There are about 2500 unique ingPtID in the database. Each of these ingPtID's will have a STUDY_DATE and FVC%. I would like to get the minimum FVC% & corresponding date for each ingPTID. I'm hoping that further info might help. In that case try something more like: SELECT ingPtID, STUDY_DATE, [FVC%] FROM table INNER JOIN (SELECT X.ingPtID, Min([FVC%]) As MinPct FROM table As X GROUP BY X.ingPtID) As M ON M.ingPtID = table.ingPtID And M.MinPct = table.MinPct -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|