Finding half hourly averages

I'm trying to establish the most efficient way of obtaining half hourly averages of data from an Access table. I assume i'll need to create a query with an "average" field and somehow perform an average function on the data. I have little experience in this sort of project so any advice would be much appreciated. Below is a sample of the data:

INTERVAL_DATETIME              DUID            MWH_READING      LASTCHANGED
15/12/2005 10:25:00 AM      GB02      0                       15/12/2005 2:40:05 PM
15/12/2005 10:25:00 AM      LKBONNY1      65.7853      15/12/2005 2:40:05 PM
15/12/2005 10:25:00 AM      PALOONA      20.2555      15/12/2005 2:40:05 PM
15/12/2005 10:25:00 AM      REPULSE      0              15/12/2005 2:40:05 PM
15/12/2005 10:25:00 AM      WG01      0              15/12/2005 2:40:05 PM
15/12/2005 10:25:00 AM      WOOLNTH1      52.23      15/12/2005 2:40:10 PM
15/12/2005 10:30:00 AM      BUTLERSG      10.4               15/12/2005 2:40:05 PM
15/12/2005 10:30:00 AM      CLUNY      0               15/12/2005 2:40:05 PM
15/12/2005 10:30:00 AM      GB01      0                       15/12/2005 2:40:05 PM
15/12/2005 10:30:00 AM      GB02      0                       15/12/2005 2:40:05 PM
15/12/2005 10:30:00 AM      LKBONNY1      66.3033      15/12/2005 2:40:05 PM

...and so on

At the moment the data is recorded at intervals of five minutes as seen above. I need to find half hourly averages of this five minute data.

Thanks,

Paul
paulkramerAsked:
Who is Participating?
 
harfangConnect With a Mentor Commented:
To change your interval field to half-hour intervals, you need to round it. In your sample, you want to round up, which Access doesn't provide as such, but you can use -Int(-x) to round up: Int() rounds strictly down, and by reversing the sign, you can round up.

There are 48 one-half-hour slices in a day, so that:

    CVDate(-Int(-INTERVAL_DATETIME*48)/48)

... is the interval field, rounded to the next 30 minutes boundary. You can use this expression in a total query, much like Flyster showed:

    SELECT
        CVDate(-Int(-INTERVAL_DATETIME*48)/48) As INTERVAL_SLICE,
        DUID,
        Avg(MWH_READING) As AVERAGE_READING
    FROM <your table name here>
    GROUP BY
        CVDate(-Int(-INTERVAL_DATETIME*48)/48),
        DUID

Good luck
(°v°)
0
 
SharathData EngineerCommented:
Can you post the expected result?
0
 
paulkramerAuthor Commented:
An expected result would be:

INTERVAL_DATETIME              DUID            MWH_READING

15/12/2005 10:30:00 AM      GB02                0      
15/12/2005 10:30:00 AM      LKBONNY1       66.0443

etc...

These values are the MWH reading averages for DUID's from 10:05am - 10:30am. The next half hourly average would require data from 10:35 - 11am.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
FlysterCommented:
Is this what you're looking for:

SELECT YourTable.[DUID], Count(YourTable.DUID) AS CountOfDUID, Avg(YourTable.[MWH_READING]) AS AvgOfMWH_READING
FROM YourTable
GROUP BY YourTable.[DUID]
HAVING (((Count(YourTable.DUID))>1));

Flyster
0
 
Gustav BrockConnect With a Mentor CIOCommented:
Do something like the SQL below.

/gustav
SELECT
  CDate(Int(INTERVAL_DATETIME * 48) / 48) AS HALFHOUR_DATETIME,
  DUID,
  Avg(MWH_READING) AS Avg_MWH_READING
FROM
  tblYourTable
GROUP BY
  CDate(Int(INTERVAL_DATETIME * 48) / 48) AS HALFHOUR_DATETIME,
  DUID;
 
or:
 
SELECT
  CDate(Int(INTERVAL_DATETIME * 48) / 48) AS HALFHOUR_DATETIME,
  Avg(MWH_READING) AS Avg_MWH_READING
FROM
  tblYourTable
GROUP BY
  CDate(Int(INTERVAL_DATETIME * 48) / 48) AS HALFHOUR_DATETIME;

Open in new window

0
 
harfangCommented:
Hi gustav, we were thinking along the same lines again... good to see you!
(°v°)
0
 
Gustav BrockCIOCommented:
Well, quite busy with real work but drop from time to time.

Seems like this is the solution to Paul's question!
Should not that you round up by the half hour where my suggestion rounds down.

/gustav
0
 
paulkramerAuthor Commented:
This did the trick. Thanks guys.

Do you know of a good resource that would help with picking up Access SQL querying knowledge? I'd like to be able to construct the above query from scratch.

Paul
0
 
Gustav BrockCIOCommented:
Many sources exist. Here is one:

http://www.w3schools.com/sql/default.asp

Also the on-line help of Access has many examples.

/gustav
0
 
harfangCommented:
I learned a lot about Access SQL by using the assistants and then studying the resulting queries in SQL view. As Gustav mentioned, there are good examples in the help files, and it's also very useful to take the sample database apart...

As for books, it's too personal. If you have a good specialized book store in your area, take a couple of hours to browse. There should be one with the right level and the right tone for you.

(°v°)
0
 
paulkramerAuthor Commented:
I'm still a little confused about the query logic here.. The five minute interval date time slices are multiplied by 48 and then divided by 48. This whole process is thoroughly confusing me.
0
 
Gustav BrockCIOCommented:
That's an old trick which can be used in many situations to round down.

The day count is a numeric integer, thus hours are decimals, 1/24 of a day, and half-hours are 1/48 of a day.
By multiplying with 48 you will get the half-hour count as an integer. Time portions less than half an hour (quarters, minutes) are still decimals and these you remove with Int(). Now to get the value back to a day count numeric value with decimals, divide by 48.
Finally, convert this numeric value to a date/time value with CDate().

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.