Prev: Report + chart
Next: Exclude data from field, when another field equals a certain numbe
From: woods1119 on 28 Apr 2010 10:10
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.
From: Daryl S on 28 Apr 2010 10:25
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.
From: Tom van Stiphout on 28 Apr 2010 10:33
On Wed, 28 Apr 2010 07:10:10 -0700, woods1119
<woods1119(a)discussions.microsoft.com> 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.
From: woods1119 on 28 Apr 2010 10:40
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.
From: Marshall Barton on 28 Apr 2010 12:27
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]
 |  Next  |  Last
Pages: 1 2
Prev: Report + chart
Next: Exclude data from field, when another field equals a certain numbe