We help IT Professionals succeed at work.

Finding half hourly averages

on
Medium Priority
433 Views
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:

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
Comment
Watch Question

View Solutions Only

Data Engineer
CERTIFIED EXPERT

Commented:
Can you post the expected result?

Commented:
An expected result would be:

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

Commented:
Is this what you're looking for:

FROM YourTable
GROUP BY YourTable.[DUID]
HAVING (((Count(YourTable.DUID))>1));

Flyster
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Do something like the SQL below.

/gustav
``````SELECT
CDate(Int(INTERVAL_DATETIME * 48) / 48) AS HALFHOUR_DATETIME,
DUID,
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,
FROM
tblYourTable
GROUP BY
CDate(Int(INTERVAL_DATETIME * 48) / 48) AS HALFHOUR_DATETIME;
``````

Not the solution you were looking for? Getting a personalized solution is easy.

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,
GROUP BY
CVDate(-Int(-INTERVAL_DATETIME*48)/48),
DUID

Good luck
(°v°)

Commented:
Hi gustav, we were thinking along the same lines again... good to see you!
(°v°)
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Many sources exist. Here is one:

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

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

/gustav

Commented:
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°)

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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile