Moving Average

[Ted]

I have a table of data that I need to calculate new
averages on every week. My data looks like this
Emp# WeekEnding Actual Allowed Percent Running
27 2/2/2000 21.23 20.81 98.02
27 2/19/2000 22.35 22.13 99.02
27 2/26/2000 18.27 18.09 99.01
27 3/4/2000 13.28 12.75 96.01
27 3/11/2000 13.78 13.78 100.00
27 4/1/2000 19.98 19.78 99.00
Percent is calculated from (allowed/actual)*100 Under
running I want it to calculate each average percent of
the current week and the previous week. For example Week
ending 2/19/2000 should be 98.53 ((20.81+22.13)/
(21.23+22.35))*100 and so on. Any ideas? Thanks.

 

[John Viescas]

Assuming your sample data should start with WeekEnding 2/12/2000,

SELECT [Emp#], WeekEnding, Actual, Allowed, (Allowed/Actual) * 100 As
Percent, (((Allowed/Actual) * 100) + NZ((SELECT (Allowed/Actual) * 100
FROM MyTable As T2
WHERE T2.[Emp#] = MyTable.[Emp#] AND T2.WeekEnding = (MyTable.WeekEnding -
7)), 0)) / 2 As Running
FROM MyTable
ORDER By [Emp#], WeekEnding;

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[MGFoster]

I have a table of data that I need to calculate new
averages on every week. My data looks like this
Emp# WeekEnding Actual Allowed Percent Running
27 2/2/2000 21.23 20.81 98.02
27 2/19/2000 22.35 22.13 99.02
27 2/26/2000 18.27 18.09 99.01
27 3/4/2000 13.28 12.75 96.01
27 3/11/2000 13.78 13.78 100.00
27 4/1/2000 19.98 19.78 99.00
Percent is calculated from (allowed/actual)*100 Under
running I want it to calculate each average percent of
the current week and the previous week. For example Week
ending 2/19/2000 should be 98.53 ((20.81+22.13)/
(21.23+22.35))*100 and so on. Any ideas? Thanks.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Read this article on MS KB for an example how to do this. You'll have
to change the calculation so it adds the previous record's values to the
current record's values instead of just retrieving the previous record's
values.

http://support.microsoft.com/support/kb/articles/q101/0/81.asp

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHL+gYechKqOuFEgEQKL1ACgmOVz219MQUNmPC8JNIWJ6ajGMF0AoNKw
CCbMBrGwkDQFuIaAjZGhMe6h
=RHn3
-----END PGP SIGNATURE-----

 

[Tom Ellison]

Dear Ted:

I'm going to assume the first "WeekEnding" value is really 2/12/2000
and that you are actually doing real weeks at 7 day intervals.

Even with this assumption, it appears you may skip weeks. For
example, the 4/1/2000 value makes it appear you have skipped the weeks
ending 3/18 and 3/25. Now, when this happens, should it figure there
was 0 Actual and 0 Allowed for the week where the data was missing, or
should it use the previous week as 3/11? That is, what is your
definition of "previous"? 7 days before? Or the preceding recorded
week?

If you answer this, I think I can build this for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts