By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

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

P: 22
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
Share this Question
Share on Google+
12 Replies


FishVal
Expert 2.5K+
P: 2,653
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

P: 22
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
Thanks Fish, very helpful.

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
Expert 2.5K+
P: 2,653
Thanks Fish, very helpful.

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

P: 22
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
Expert 2.5K+
P: 3,532
"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
Expert 2.5K+
P: 2,653
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

P: 22
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
Expert 2.5K+
P: 2,653
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

P: 22
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
2. Download Date, Date/Time
3. MWHRS, Numeric

For each unit number, there are many download date's each with one measurement of MWHRS. For each unit, I need to find the minimum download date and associated MWHRS, and the maximum download date and associated MWHRS. Then calculate (MWHRS_2-MWHRS_1)/(DownloadDate_2-DownloadDate_1).

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
Expert 2.5K+
P: 2,653
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
  10.         .CursorType = adOpenDynamic
  11.         .LockType = adLockOptimistic
  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

P: 22
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
  10.         .CursorType = adOpenDynamic
  11.         .LockType = adLockOptimistic
  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

rs As New ADODB.Recordset

is ADODB a add in that I may be missing? I'm using Access 97
Aug 28 '08 #12

FishVal
Expert 2.5K+
P: 2,653
In VBE window open MainMenu.Tools->References.
Check "Microsoft ActiveX Data Objects x.x Library".

Regards,
Fish
Aug 31 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.