[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Finding half hourly averages

Posted on 2009-02-15
12
Medium Priority
?
414 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:paulkramer
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 23646759
Can you post the expected result?
0
 

Author Comment

by:paulkramer
ID: 23646780
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
 
LVL 22

Expert Comment

by:Flyster
ID: 23647145
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 800 total points
ID: 23648581
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
 
LVL 58

Accepted Solution

by:
harfang earned 1200 total points
ID: 23648676
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
 
LVL 58

Expert Comment

by:harfang
ID: 23648692
Hi gustav, we were thinking along the same lines again... good to see you!
(°v°)
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 23648854
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
 

Author Closing Comment

by:paulkramer
ID: 31547167
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 23656741
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
 
LVL 58

Expert Comment

by:harfang
ID: 23659305
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
 

Author Comment

by:paulkramer
ID: 24083127
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 24084382
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month20 days, 12 hours left to enroll

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question