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.

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.

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.

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.

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]

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.

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

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]