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

[denveromlp]

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!

[FishVal]

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.

SELECT 
((SELECT TOP 1 t.dblMeas FROM t
WHERE Year(t.dteDate)=Year(Date()) ORDER BY t.dteDate)+
(SELECT TOP 1 t.dblMeas FROM t
WHERE Year(t.dteDate)=Year(Date()) ORDER BY t.dteDate DESC))/2 AS Expr1
FROM t;
 

Regards,
Fish

[denveromlp]

Hello.

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

SELECT 
((SELECT TOP 1 t.dblMeas FROM t
WHERE Year(t.dteDate)=Year(Date()) ORDER BY t.dteDate)+
(SELECT TOP 1 t.dblMeas FROM t
WHERE Year(t.dteDate)=Year(Date()) ORDER BY t.dteDate DESC))/2 AS Expr1
FROM t;
 

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.

[FishVal]

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?

[denveromlp]

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.

[missinglinq]

"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

[FishVal]

Looking to use days.

SELECT TOP 1 
((SELECT TOP 1 t.dblMeas FROM t WHERE Year(t.dteDate)=Year(Date())
ORDER BY t.dteDate)+
(SELECT TOP 1 t.dblMeas FROM t WHERE Year(t.dteDate)=Year(Date())
ORDER BY t.dteDate DESC))/
(SELECT DateDiff("d",Min(t.dteDate),Max(t.dteDate)) FROM t
WHERE Year(t.dteDate)=Year(Date())) AS Expr1
FROM t;
 

More elegant it could be implemented using VBA.

Regards,
Fish

[denveromlp]

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?

[FishVal]

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

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[denveromlp]

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

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
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.

[FishVal]

Ok.

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

In code module:

Public Function Derivative(lngUnitNumber As Long) As Double
 
    Dim rs As New ADODB.Recordset
    Dim dteTemp As Date, dblTemp As Double
 
    With rs
 
        'opens recordset filtered by particular unit number ordered by date
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open "SELECT * FROM t WHERE lngUnitNumber=" & _
            lngUnitNumber & " ORDER BY dteDate;"
 
        'get values of first record (earliest date)
        dteTemp = !dteDate
        dblTemp = !dblMWHRS
 
        'move to last record (latest date) and calculate derivative
        .MoveLast
        Derivative = (!dblMWHRS - dblTemp) / DateDiff("d", dteTemp, !dteDate)
        .Close
 
    End With
 
    Set rs = Nothing
 
End Function
 

Query:

SELECT DISTINCT t.lngUnitNumber, Derivative(t.lngUnitNumber) AS Derivative
FROM t;
 

Regards,
Fish

[denveromlp]

Ok.

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

In code module:

Public Function Derivative(lngUnitNumber As Long) As Double
 
    Dim rs As New ADODB.Recordset
    Dim dteTemp As Date, dblTemp As Double
 
    With rs
 
        'opens recordset filtered by particular unit number ordered by date
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open "SELECT * FROM t WHERE lngUnitNumber=" & _
            lngUnitNumber & " ORDER BY dteDate;"
 
        'get values of first record (earliest date)
        dteTemp = !dteDate
        dblTemp = !dblMWHRS
 
        'move to last record (latest date) and calculate derivative
        .MoveLast
        Derivative = (!dblMWHRS - dblTemp) / DateDiff("d", dteTemp, !dteDate)
        .Close
 
    End With
 
    Set rs = Nothing
 
End Function
 

Query:

SELECT DISTINCT t.lngUnitNumber, Derivative(t.lngUnitNumber) AS Derivative
FROM t;
 

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

[FishVal]

In VBE window open MainMenu.Tools->References.
Check "Microsoft ActiveX Data Objects x.x Library".

Regards,
Fish