Prev: id is not an index in this table
Next: linking tables from different Access templates?
From: KenSheridan via AccessMonster.com on 17 Mar 2010 17:32
Group the query on the date/time column formatted to the hour, e.g.

SELECT Format([SampleTime],”mm/dd/yyyy hh” AS [Sample Hour],
AVG([SiteName_EC]) AS [Average Quality]
FROM [YourTable]
GROUP BY Format([SampleTime],”yyyymmddhh”),
Format([SampleTime],”mm/dd/yyyy hh”);

The first group level is purely to ensure the correct order of the results.

Ken Sheridan
Stafford, England

Calif_Mike wrote:
>I have water quality data that is stored at 15 minute intervals. I would
>like to query it as an hourly average. Date and time stored together as
>mm/dd/yyyy hh:mm. Water quality is stored as "SiteName_EC".
>
>The query wisard will develop daily, monthly and yearly but not hourly
>queries.
>
>Thanks,
>Mike

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

From: KARL DEWEY on 17 Mar 2010 18:26
I am guessing but maybe like this using AVG([Quality]) AS [Average Quality]
if the field the reading is sotred in is named Quality --
SELECT [SiteName_EC], Format([SampleTime],”mm/dd/yyyy hh” AS [Sample Hour],
AVG([Quality]) AS [Average Quality]
FROM [YourTable]
GROUP BY [SiteName_EC], Format([SampleTime],”yyyymmddhh”),
Format([SampleTime],”mm/dd/yyyy hh”);

--
Build a little, test a little.


"KenSheridan via AccessMonster.com" wrote:

> Group the query on the date/time column formatted to the hour, e.g.
>
> SELECT Format([SampleTime],”mm/dd/yyyy hh” AS [Sample Hour],
> AVG([SiteName_EC]) AS [Average Quality]
> FROM [YourTable]
> GROUP BY Format([SampleTime],”yyyymmddhh”),
> Format([SampleTime],”mm/dd/yyyy hh”);
>
> The first group level is purely to ensure the correct order of the results.
>
> Ken Sheridan
> Stafford, England
>
> Calif_Mike wrote:
> >I have water quality data that is stored at 15 minute intervals. I would
> >like to query it as an hourly average. Date and time stored together as
> >mm/dd/yyyy hh:mm. Water quality is stored as "SiteName_EC".
> >
> >The query wisard will develop daily, monthly and yearly but not hourly
> >queries.
> >
> >Thanks,
> >Mike
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
>
> .
>
From: Calif_Mike on 17 Mar 2010 18:51
This is getting me the average EC data that I need. The only problem is the
date and hour are coming out as text. If I run the query in as a Make Table
query and then set the Time by Hour field to Date/time format, it errors and
erases the dates and times.

SELECT DISTINCTROW Format$([Waterquality2008].["Time"],"mm/dd/yy hh") AS
[Time By Hour], Avg(Waterquality2008.["HardingDrain_EC"]) AS [Avg Of
"HardingDrain_EC"], Avg(Waterquality2008.["L55D22_EC"]) AS [Avg Of
"L55D22_EC"] INTO [Harding_L5 Hourly EC]
FROM Waterquality2008
GROUP BY Format$([Waterquality2008].["Time"],"mm/dd/yy hh");

"KARL DEWEY" wrote:

> I am guessing but maybe like this using AVG([Quality]) AS [Average Quality]
> if the field the reading is sotred in is named Quality --
> SELECT [SiteName_EC], Format([SampleTime],”mm/dd/yyyy hh” AS [Sample Hour],
> AVG([Quality]) AS [Average Quality]
> FROM [YourTable]
> GROUP BY [SiteName_EC], Format([SampleTime],”yyyymmddhh”),
> Format([SampleTime],”mm/dd/yyyy hh”);
>
> --
> Build a little, test a little.
>
>
> "KenSheridan via AccessMonster.com" wrote:
>
> > Group the query on the date/time column formatted to the hour, e.g.
> >
> > SELECT Format([SampleTime],”mm/dd/yyyy hh” AS [Sample Hour],
> > AVG([SiteName_EC]) AS [Average Quality]
> > FROM [YourTable]
> > GROUP BY Format([SampleTime],”yyyymmddhh”),
> > Format([SampleTime],”mm/dd/yyyy hh”);
> >
> > The first group level is purely to ensure the correct order of the results.
> >
> > Ken Sheridan
> > Stafford, England
> >
> > Calif_Mike wrote:
> > >I have water quality data that is stored at 15 minute intervals. I would
> > >like to query it as an hourly average. Date and time stored together as
> > >mm/dd/yyyy hh:mm. Water quality is stored as "SiteName_EC".
> > >
> > >The query wisard will develop daily, monthly and yearly but not hourly
> > >queries.
> > >
> > >Thanks,
> > >Mike
> >
> > --
> > Message posted via AccessMonster.com
> > http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
> >
> > .
> >
From: John W. Vinson on 17 Mar 2010 19:28
On Wed, 17 Mar 2010 15:51:02 -0700, Calif_Mike
<CalifMike(a)discussions.microsoft.com> wrote:

>This is getting me the average EC data that I need. The only problem is the
>date and hour are coming out as text. If I run the query in as a Make Table
>query and then set the Time by Hour field to Date/time format, it errors and
>erases the dates and times.
>
>SELECT DISTINCTROW Format$([Waterquality2008].["Time"],"mm/dd/yy hh") AS
>[Time By Hour], Avg(Waterquality2008.["HardingDrain_EC"]) AS [Avg Of
>"HardingDrain_EC"], Avg(Waterquality2008.["L55D22_EC"]) AS [Avg Of
>"L55D22_EC"] INTO [Harding_L5 Hourly EC]
>FROM Waterquality2008
>GROUP BY Format$([Waterquality2008].["Time"],"mm/dd/yy hh");

If you in fact want a second table with the hourly averages - which I would
NOT recommend, in that it's storing data redundantly and also storing derived
data!!!! - you can create a calculated Date/Time field with an expression like
DateValue(Fix(CDbl([fieldname])*24)/24.)

I'm very queasy about the quoted strings in your fieldnames though!!!
--

John W. Vinson [MVP]
From: KenSheridan via AccessMonster.com on 17 Mar 2010 20:12
The following function would round a date/time value down to the nearest hour:


Public Function RoundToHour(dtmDateTime As Date) As Date

RoundToHour = CDate(Format(dtmDateTime, "yyyy-mm-dd hh:00:00"))

End Function

Ken Sheridan
Stafford, England

Calif_Mike wrote:
>This is getting me the average EC data that I need. The only problem is the
>date and hour are coming out as text. If I run the query in as a Make Table
>query and then set the Time by Hour field to Date/time format, it errors and
>erases the dates and times.
>
>SELECT DISTINCTROW Format$([Waterquality2008].["Time"],"mm/dd/yy hh") AS
>[Time By Hour], Avg(Waterquality2008.["HardingDrain_EC"]) AS [Avg Of
>"HardingDrain_EC"], Avg(Waterquality2008.["L55D22_EC"]) AS [Avg Of
>"L55D22_EC"] INTO [Harding_L5 Hourly EC]
>FROM Waterquality2008
>GROUP BY Format$([Waterquality2008].["Time"],"mm/dd/yy hh");
>
>> I am guessing but maybe like this using AVG([Quality]) AS [Average Quality]
>> if the field the reading is sotred in is named Quality --
>[quoted text clipped - 26 lines]
>> > >Thanks,
>> > >Mike

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: id is not an index in this table
Next: linking tables from different Access templates?