Re: Urgent help

Hi Bob-

Thanks for this reply. So I guess my solution would only work in MS Access...

Betsy

"Bob Badour" <bbadour_at_golden.net> wrote in message news:mGbUa.1244$%M7.223142044_at_mantis.golden.net...
> Hi Betsy,
>
> TRANSFORM / PIVOT is not SQL. It is a vendor-specific extension to the
> language.
>
> Bob
>
> "Betsy" <Betwalk_at_sisna.com> wrote in message
> news:bfra9l$rpf$1_at_balder.stud.idb.hist.no...
> > As posted in reply to the OP's request on another board, this can be
> treated
> > as a simple crosstab query. The SQL looks like this:
> >
> > TRANSFORM First(tblBuckets.WaterLevel) AS FirstOfWaterLevel
> > SELECT tblBuckets.BucketID
> > FROM tblBuckets
> > GROUP BY tblBuckets.BucketID
> > PIVOT tblBuckets.Time;
> >
> > As a reminder to the OP, "Time" is not a good choice for a field name.
It
> > would be
> > better to call it "TimeCheck" or something that is more obviously
> > user-created.
> >
> > HTH- Betsy
> >
> > "Alan" <alanpltse_at_yahoo.com.au> wrote in message
> > news:3f1f1b9a_at_news.comindico.com.au...
> > > I have the following table BucketTable :
> > >
> > > BucketID Time WaterLevel
> > > ------------------------------------------
> > > 1 9:00 Empty
> > > 1 10:00 Half
> > > 1 11:00 Full
> > > 2 9:20 Empty
> > > 2 11:30 Half
> > > 2 13:00 Full
> > > 3 11:00 Empty
> > > 3 14:10 Half
> > > 3 16:00 Full
> > >
> > > This table keeps the timestamp of the water level of the buckets.
> > > I want to write a SQL statement to show the following :
> > >
> > > BucketID Empty Half Full
> > > 1 9:00 10:00 11:00
> > > 2 9:20 11:30 13:00
> > > 3 11:00 14:10 16:00
> > >
> > > I tried
> > >
> > > Select BucketID,
> > > IF(WaterLevel='Empty', Time, ''),
> > > IF(WaterLevel='Half', Time, '')
> > > IF(WaterLevel='Full', Time, '')
> > > FROM Bucket
> > > ORDER BY BucketID;
> > >
> > > However, it did not work as it gave me 9 records rather than 3.
> > > I also tried DISTINCT and GROUP BY, it gave me 3 records but only with
> the
> > > Empty column has time.
> > >
> > >
> >
> >
>
>
Received on Fri Jul 25 2003 - 16:30:22 CEST