Running Averages

[Guest]

How do you calculate a running average in a query? I am making a chart and
need the running average information from a query I am using. For example,
if I have this information in the results of my query:

Date Orders

1/1/01 1
1/2/01 4
1/3/01 6
1/4/01 4

I want another field that calculates the running average of orders:

Date Orders Running Average
1/1/01 1 1 (1/1)
1/2/01 4 2.5 (5/2)
1/3/01 6 3.66 (11/3)
1/4/01 4 3.75 (15/4)

I know in Excel you would enter the formula =Average($B$2:B3)and drag it,
but how do you get these same results in Access? I would rather it not be
calculated in a report, but based off of a query that calculates it. Is
there a formula? However, if there is no other way, how do you do it in a
report also? I see running sum, what about running average?

 

[[MVP] S.Clark]

Unfortunately, Access was designed as a data repository(with lots of extra
benefits), but it's not so good with Statistics. Excel is great with
Statistics, but stinks as a relational database.

This is a total hack, but it might work in your situation:

Select dDate, Orders, Count(*) / DSum("Orders", "Tablename", "dDate <=
#" & dDate & "#") as RunAvg from Tablename

Because "Date" is a reserved word in access, dDate represents your date
field name.
Obviously(I hope) replace tablename to be your table name.

I didn't test this code, so errors not covered by warranty. Please
understand all moving parts before operating.

 

[James Hahn]

What you have described is not a running average. It is an average
calculated at several different points in the data base. You use a running
average when you only have access to the average, the latest value and the
count, and you would calculate it like this.
Date Orders Count Running Average
1/1/01 1 1 1 (1/1) or (0 + (1-0)/1)
1/2/01 4 2 2.5 (1 + (4-1)/2)
1/3/01 6 3 3.66 (2.5 + (6-2.5)/3)
1/4/01 4 4 3.75 (3.66 + (4-3.66)/4))

In your case you can use the sum and the count to calculate the value for
each data point.
Date Orders Sum Count Average
1/1/01 1 1 1 1 (1/1)
1/2/01 4 5 2 2.5 (5/2)
1/3/01 6 11 3 3.66 (11/3)
1/4/01 4 15 4 3.75 (15/4))