Hourly Avg

[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

 

[Jerry Whittle]

A report can group things down to the minute and hour level.

 

[Calif_Mike]

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

 

[John Spencer]

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

 

[Arvin Meyer [MVP]]

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.

 

[KARL DEWEY]

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.

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

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

--



.

 

[Calif_Mike]

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 --
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.

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

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

--



.

 

[John W. Vinson]

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!!!

 

[James A. Fortune]

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

That's a very nice, elegant function!

James A. Fortune
(e-mail address removed)