Rolling 12 month average

[handytime]

Stuck! Current query is by Supplier, Year-Month Percent Acceptance for that Month. Need to Add Column with 12 Month Rolling Average. Results needed:
Supplier mm-yy Avg 12_Mo_Avg
abc Feb-03 100.00% 100.00%
abc Mar-03 98.50% 99.25%
abc Mar-03 96.80% 98.40%
abc Apr-03 100.00% 98.80%
abc May-03 100.00% 99.06%

 

[GumbyD]

OK - here is some very raw code that might help you. I was not sure exactly what you had, but hopefully this will get you started. I created a function called 'RollAvg' to get the rolling average. You will need to feed it a date parameter and then it will get the average for the values in the 12 months prior to that date (so in your query you would have a field like RollingAverage:RollAvg([YourDateField]). There are no error traps or anything in this code yet so you will need to finish it this is just the bare bones. I hope it helps you.

Public Function RollAvg(SDate As Date) As Double
Dim SQL As String
Dim rstAvg As Recordset

SQL = "SELECT Avg([Your Table Name].[Your monthly Value field Name]) AS AvgOfAvg " & _
"FROM [Your Table Name]" & _
"WHERE ((([Your Table Name].Date) Between dateadd('yyyy',-1,#" & SDate & "#) + 1 and #" & SDate & "#));"

Set rstAvg = CurrentDb.OpenRecordset(SQL)

RollAvg = rstAvg!AvgofAvg

rstAvg.Close

End Function

Good Luck!

GumbyD

 

[dcx693]

Whew. I solved the problem a different way using two queries. I was able to come up with the output pictured in the attached jpg.

Short explanation:

First, I added an autonumber transaction field to the data table. Doesn't look like your mmm-yy field data is unique, therefore, without a unique identifier for each transaction, it's hard to know which Mar-03 transaction came first, and therefore which should or should not include the other in the rolling average. I used "SDate" and "SAverage" to avoid using "Date" and "Avg" as field names, since they are reserved words.

Next, I created a query with two copies of the data table. I did not join them using equijoins, but two non-equijoins.

Finaly, I did a totals query off that first query and used the aggregate Avg function to get the rolling average.

It's pretty hard to describe verbally. I'll attach pictures of the queries so you can see them visually.

Don't know if that was overall easier than using a custom function. Please comment everyone.

 

[dcx693]

Here is a picture of the first query.

 

[dcx693]

Here is a picture of the second query that, when run, produces the output in my first post.

 

[GumbyD]

I like both of the solutions. In my mind it is all about getting the results. If your queries are working why switch.

GumbyD