Hourly Avg

  • Thread starter Thread starter Calif_Mike
  • Start date Start date
C

Calif_Mike

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
 
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.
 
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 said:
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 said:
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
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
Click to expand...
 
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");
Click to expand...

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!!!
 
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
Click to expand...

That's a very nice, elegant function!

James A. Fortune
(e-mail address removed)
 
Back
Top