469,592 Members | 1,769 Online

# Trying to calculate a rolling 12 month average ( was new to access)

Hello,

I'm new to Access and trying to calculate a rolling 12 month average from some time data. Each data point is a date and a measurement taken at that date. As far as I can tell, the only way to take the rolling average is to create a make-table of all the data points within the last year. Then create a query to pull out the minimum date, create a second query to pull out the maximum date, create a thrid query to pull out the measurement from the minimum date, and create a forth query to pull out the measurement for the maximum date. Finally, create a fith query to average the min/max measurements.

Seems like there should be a more efficient way to do this. Any suggestions?
Thanks!
Aug 26 '08 #1
12 8635
FishVal
2,653 Expert 2GB
Hello,

I'm new to Access and trying to calculate a rolling 12 month average from some time data. Each data point is a date and a measurement taken at that date. As far as I can tell, the only way to take the rolling average is to create a make-table of all the data points within the last year. Then create a query to pull out the minimum date, create a second query to pull out the maximum date, create a thrid query to pull out the measurement from the minimum date, and create a forth query to pull out the measurement for the maximum date. Finally, create a fith query to average the min/max measurements.

Seems like there should be a more efficient way to do this. Any suggestions?
Thanks!
Hello.

The same may be done by a single query with two subqueries.

Expand|Select|Wrap|Line Numbers
1. SELECT
2. ((SELECT TOP 1 t.dblMeas FROM t
3. WHERE Year(t.dteDate)=Year(Date()) ORDER BY t.dteDate)+
4. (SELECT TOP 1 t.dblMeas FROM t
5. WHERE Year(t.dteDate)=Year(Date()) ORDER BY t.dteDate DESC))/2 AS Expr1
6. FROM t;
7.
Regards,
Fish
Aug 26 '08 #2
Hello.

The same may be done by a single query with two subqueries.

Expand|Select|Wrap|Line Numbers
1. SELECT
2. ((SELECT TOP 1 t.dblMeas FROM t
3. WHERE Year(t.dteDate)=Year(Date()) ORDER BY t.dteDate)+
4. (SELECT TOP 1 t.dblMeas FROM t
5. WHERE Year(t.dteDate)=Year(Date()) ORDER BY t.dteDate DESC))/2 AS Expr1
6. FROM t;
7.
Regards,
Fish

Although I didn't quite describe the averaging clearly. I don't want to take the average of the two measurements, I actually want to take the time derivative. Instead of (meas1+meas2)/2, I want (meas2-meas1)/(date2-date1). To find out at what rate the measurement is increasing.
Aug 26 '08 #3
FishVal
2,653 Expert 2GB

Although I didn't quite describe the averaging clearly. I don't want to take the average of the two measurements, I actually want to take the time derivative. Instead of (meas1+meas2)/2, I want (meas2-meas1)/(date2-date1). To find out at what rate the measurement is increasing.
Ok.

Next time please don't hesitate to call a thing by its name :D
In what units you need to calculate time differential - hours, days, months?
Aug 26 '08 #4
Ok.

Next time please don't hesitate to call a thing by its name :D
In what units you need to calculate time differential - hours, days, months?
Looking to use days.
Aug 26 '08 #5
missinglinq
3,532 Expert 2GB
"New to Access" tells our members nothing of your problem, and so I have changed the title. Please, in the future, remember to provide a meaningful Title for any threads you start! This helps to ensure that other members, and also the general public, will have a better chance of finding answers to similar questions. It is difficult for the experts to answer questions when there is not enough details provided to understand the problem.

Please take the time to read the Posting Guidelines, paying particular attention to Give a clear title to your question.

Thank you and Welcome to Bytes!!

Linq ;0)>

Moderator
Aug 26 '08 #6
FishVal
2,653 Expert 2GB
Looking to use days.
Expand|Select|Wrap|Line Numbers
1. SELECT TOP 1
2. ((SELECT TOP 1 t.dblMeas FROM t WHERE Year(t.dteDate)=Year(Date())
3. ORDER BY t.dteDate)+
4. (SELECT TOP 1 t.dblMeas FROM t WHERE Year(t.dteDate)=Year(Date())
5. ORDER BY t.dteDate DESC))/
6. (SELECT DateDiff("d",Min(t.dteDate),Max(t.dteDate)) FROM t
7. WHERE Year(t.dteDate)=Year(Date())) AS Expr1
8. FROM t;
9.
More elegant it could be implemented using VBA.

Regards,
Fish
Aug 26 '08 #7
ok, we're getting closer...thanks for your help with this

I pulled out all of the "WHERE Year(t.dteDate)=Year(Date())" clauses because the data in the table is already only data for the previous 12 months. Also, it is a rolling 12 months, not a calendar year, so some of the data is from the last part of 2007 and the rest from 2008. After removing where statements, seems to be running correctly.

The only problem is that the data should be grouped by another field, call it fieldX. I need to look at the measurement from the earliest date and latest date within the different entries of fieldx, not on the whole table.

make sense?
Aug 26 '08 #8
FishVal
2,653 Expert 2GB
Please include the MetaData for all relevant datasets with clear explanation of important fields purpose. Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
1. Field; Type; IndexInfo
2. StudentID; AutoNumber; PK
3. Family; String; FK
4. Name; String
5. University; String; FK
6. Mark; Numeric
7. LastAttendance; Date/Time
Aug 27 '08 #9
Please include the MetaData for all relevant datasets with clear explanation of important fields purpose. Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
1. Field; Type; IndexInfo
2. StudentID; AutoNumber; PK
3. Family; String; FK
4. Name; String
5. University; String; FK
6. Mark; Numeric
7. LastAttendance; Date/Time
1. Unit Number, Numeric
3. MWHRS, Numeric

The previous code took the maximum download date out of all of the units, the minimum download date out of all of the units and the corresponding MWHRS. So I ended up getting the same output for each unit number.
Aug 27 '08 #10
FishVal
2,653 Expert 2GB
Ok.

As I've already posted - SQL/VBA hybrid solution will be preferrable.

In code module:
Expand|Select|Wrap|Line Numbers
1. Public Function Derivative(lngUnitNumber As Long) As Double
2.
3.     Dim rs As New ADODB.Recordset
4.     Dim dteTemp As Date, dblTemp As Double
5.
6.     With rs
7.
8.         'opens recordset filtered by particular unit number ordered by date
9.         .ActiveConnection = CurrentProject.Connection
12.         .Open "SELECT * FROM t WHERE lngUnitNumber=" & _
13.             lngUnitNumber & " ORDER BY dteDate;"
14.
15.         'get values of first record (earliest date)
16.         dteTemp = !dteDate
17.         dblTemp = !dblMWHRS
18.
19.         'move to last record (latest date) and calculate derivative
20.         .MoveLast
21.         Derivative = (!dblMWHRS - dblTemp) / DateDiff("d", dteTemp, !dteDate)
22.         .Close
23.
24.     End With
25.
26.     Set rs = Nothing
27.
28. End Function
29.
Query:
Expand|Select|Wrap|Line Numbers
1. SELECT DISTINCT t.lngUnitNumber, Derivative(t.lngUnitNumber) AS Derivative
2. FROM t;
3.
Regards,
Fish
Aug 28 '08 #11
Ok.

As I've already posted - SQL/VBA hybrid solution will be preferrable.

In code module:
Expand|Select|Wrap|Line Numbers
1. Public Function Derivative(lngUnitNumber As Long) As Double
2.
3.     Dim rs As New ADODB.Recordset
4.     Dim dteTemp As Date, dblTemp As Double
5.
6.     With rs
7.
8.         'opens recordset filtered by particular unit number ordered by date
9.         .ActiveConnection = CurrentProject.Connection
12.         .Open "SELECT * FROM t WHERE lngUnitNumber=" & _
13.             lngUnitNumber & " ORDER BY dteDate;"
14.
15.         'get values of first record (earliest date)
16.         dteTemp = !dteDate
17.         dblTemp = !dblMWHRS
18.
19.         'move to last record (latest date) and calculate derivative
20.         .MoveLast
21.         Derivative = (!dblMWHRS - dblTemp) / DateDiff("d", dteTemp, !dteDate)
22.         .Close
23.
24.     End With
25.
26.     Set rs = Nothing
27.
28. End Function
29.
Query:
Expand|Select|Wrap|Line Numbers
1. SELECT DISTINCT t.lngUnitNumber, Derivative(t.lngUnitNumber) AS Derivative
2. FROM t;
3.
Regards,
Fish
I'm getting a compliation error "User-defined data type not defined" on this line

is ADODB a add in that I may be missing? I'm using Access 97
Aug 28 '08 #12
FishVal
2,653 Expert 2GB
Check "Microsoft ActiveX Data Objects x.x Library".

Regards,
Fish
Aug 31 '08 #13