Re: Urgent help
Date: Fri, 25 Jul 2003 07:10:41 -0600
Message-ID: <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
HTH- Betsy
"Alan" <alanpltse_at_yahoo.com.au> wrote in message
news:3f1f1b9a_at_news.comindico.com.au...
better to call it "TimeCheck" or something that is more obviously
user-created.
> 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 - 15:10:41 CEST