Forecast in Access Form

[Alicia]

Hi, There is a function in the Data Analysis toolset in
Excel that will calculate a forecast. I want to know if
there is a way to use functions like that from Excel
inside an Access Form.
Another example: Like if there was a series of numbers in
Excel you can drag the series out and it will give you
the linear series or growth series. Can we do anything
like that in an Access Form?

Thank you.
Alicia

 

[Michel Walsh]

Hi,

The possibilities exist, FMS inc. (www.fmsinc.com) has a statistical
tool (http://www.fmsinc.com/products/statistics/index.html), but in fact,
total queries accept expressions in addition to the basic SUM and COUNT, so
if you are inclined to do some work by yourself...

As example, if you have fields x and y, then a line fitting can be
selected to be like


Yest = b0 + b1 * X


(you supply a X, you compute the Y estimated, which will be the best fit,
given the data in the table), with b1 and b0 supplied by


SELECT (COUNT(*)*SUM(x*y)-SUM(x)*SUM(y))/ ( COUNT(*)*SUM(x^2) -(SUM(x)^2))
As b1,
AVG(y)-b1*AVG(x) As b0
FROM myTable


(from CRC Standard Mathematical Tables and Formulae, 30th edition,
equations 7.10.2 and 7.10.3).
(http://www.crcpress.com/shopping_cart/products/product_detail.asp?sku=C2913
&parent_id=&pc=)

What is nice is that you can automatically add a GROUP BY and compute as
many curves as there are groups. A projection in time often use t rather
than x, and other curve fitting can be used, instead of a simple line, as
here. You can also get a range of Yest values (say Yest_min and Yest_max)
that correspond to a degree of confidence that the real Y would fall, 85% of
the time, between that range, as example. That is amazing about what you can
do, but yes, you CAN do it. Now, is it already all done... where would be
the fun?


Hoping it may help,
Vanderghast, Access MVP