Prev: Report + chart
Next: Exclude data from field, when another field equals a certain numbe
From: Daryl S on 28 Apr 2010 13:23
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.
From: woods1119 on 4 May 2010 12:09
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]
> .
>
From: Marshall Barton on 4 May 2010 13:55
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]
First  |  Prev  | 
Pages: 1 2
Prev: Report + chart
Next: Exclude data from field, when another field equals a certain numbe