Min value & corresponding date

[woods1119]

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.

 

[Daryl S]

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);

 

[Tom van Stiphout]

On Wed, 28 Apr 2010 07:10:10 -0700, woods1119

The general query is like this:
select * from myTable
where myField = (select Min(myField) from myTable)

-Tom.
Microsoft Access MVP

 

[woods1119]

Thanks!

It is recognizing the '%' as a syntax error though. I imagine that whoever
developed the database poorly chose to use the % in naming...?

 

[Marshall Barton]

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%]

 

[Daryl S]

Put all table names, field names, control names, etc. that have a special
character (including spaces) in square brackets. [FVC%] instead of FVC%.

 

[woods1119]

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!

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%]

 

[Marshall Barton]

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