Averaging Hourly Data

Discussion in 'Microsoft Access Queries' started by Jake D, May 17, 2010.

  1. Jake D

    Jake D Guest

    I have a lot of weather data for one month that is broken down by the minute.
    My end goal is to take the average hourly value of the column values (temp,
    humidity, etc.) and create one hourly averaged data point for each column.

    So, I am having trouble averaging integer values that occur multiple times
    within the same day and hour. Would like to apply this query to the entire
    table to average all integers for each day and hour. There are typically
    about 25 data points per hour (this number varies), for 31 days in August. I
    need to have one average hourly data point for every day in the month.

    Any help would be appreciated!

    Thanks.
     
    Jake D, May 17, 2010
    #1
    1. Advertisements

  2. On Mon, 17 May 2010 15:17:01 -0700, Jake D <Jake >
    wrote:

    >I have a lot of weather data for one month that is broken down by the minute.
    > My end goal is to take the average hourly value of the column values (temp,
    >humidity, etc.) and create one hourly averaged data point for each column.
    >
    >So, I am having trouble averaging integer values that occur multiple times
    >within the same day and hour. Would like to apply this query to the entire
    >table to average all integers for each day and hour. There are typically
    >about 25 data points per hour (this number varies), for 31 days in August. I
    >need to have one average hourly data point for every day in the month.
    >
    >Any help would be appreciated!
    >
    >Thanks.


    Try putting a calculated field in the query:
    ByTheHour: Format([datefield], "yyyymmddhh")

    This will have values such as 2009082111 for 11:00-11:59:59 AM August 21, and
    you can use it as the GROUP BY field in a totals query.

    --

    John W. Vinson [MVP]
     
    John W. Vinson, May 17, 2010
    #2
    1. Advertisements

  3. Jake D

    Jake D Guest

    John-

    Thanks for the tip, getting the date into that format was definitely the
    key. I struggled with the syntax for the group by stuff for a bit, but
    finally figured it out:

    SELECT Format([Date/Time],"yyyymmddhh") AS [Date Time], Avg([Hourly Data:
    Table].Temperature) AS [Temperature F], Avg([Hourly Data: Table].Humidity) AS
    [Humidity %] INTO Boise_Aug_Avg_Hourly_Information
    FROM [Hourly Data: Table]
    GROUP BY Format([Date/Time],"yyyymmddhh");

    This worked out perfectly for me, thanks again!

    -jake

    "John W. Vinson" wrote:

    > On Mon, 17 May 2010 15:17:01 -0700, Jake D <Jake >
    > wrote:
    >
    > >I have a lot of weather data for one month that is broken down by the minute.
    > > My end goal is to take the average hourly value of the column values (temp,
    > >humidity, etc.) and create one hourly averaged data point for each column.
    > >
    > >So, I am having trouble averaging integer values that occur multiple times
    > >within the same day and hour. Would like to apply this query to the entire
    > >table to average all integers for each day and hour. There are typically
    > >about 25 data points per hour (this number varies), for 31 days in August. I
    > >need to have one average hourly data point for every day in the month.
    > >
    > >Any help would be appreciated!
    > >
    > >Thanks.

    >
    > Try putting a calculated field in the query:
    > ByTheHour: Format([datefield], "yyyymmddhh")
    >
    > This will have values such as 2009082111 for 11:00-11:59:59 AM August 21, and
    > you can use it as the GROUP BY field in a totals query.
    >
    > --
    >
    > John W. Vinson [MVP]
    > .
    >
     
    Jake D, May 18, 2010
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Rudy

    Hourly weekly work hours

    Rudy, Jan 6, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    187
    Allen Browne
    Jan 6, 2004
  2. Michael Beatty

    Averaging the data in a row with access

    Michael Beatty, Apr 17, 2004, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    145
    Michael Beatty
    Apr 17, 2004
  3. Rog

    Hourly Data Count??

    Rog, Nov 2, 2004, in forum: Microsoft Access Queries
    Replies:
    6
    Views:
    273
  4. Guest

    Hourly volume calculation

    Guest, Mar 28, 2006, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    204
    Guest
    Mar 28, 2006
  5. Calculating hourly total

    , Mar 28, 2006, in forum: Microsoft Access Queries
    Replies:
    20
    Views:
    410
    Tom Ellison
    Mar 31, 2006
Loading...

Share This Page