Hourly Avg

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

A report can group things down to the minute and hour level.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"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

I need the data in a format that I can work with it in Excell.

"Jerry Whittle" wrote:

A report can group things down to the minute and hour level.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"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

Use the Hour function to get the hour and group by the DateValue (strip off
the time) and the Hour.

Group By DateValue(DateField), Hour(DateField)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

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

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/For...ccess/201003/1

I am unfamiliar with that wizard, but it seems that you could sort on the
date/time field, decending, then use the Daily query and change it by
dividing by 24.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Calif_Mike" wrote in message
...
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

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/For...ccess/201003/1

.

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/For...ccess/201003/1

.

On Wed, 17 Mar 2010 15:51:02 -0700, 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");

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]

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/For...ccess/201003/1