is it possible to use a form to run scenarios

[gwog]

Using Access 97.

I have a forecast accuracy database.

My users would like to be able to "play" with their numbers to see what their accuracy would be if things changed.

I have created a table tblScenario which contains the following fields:
item (Text)
histqty (number)
histfcst (number)

and a form frmScenario which has those columns and 2 calculated fields:

FA (1-((abs([histqty]-[histfcst]))/[histqty])
Con ([histqty]/[histfcst])

it also contains a summary of the fields: [histqty] [histfcst] [FA] [Con]

For example


ITEM HISTQTY HISTFCST FA CON
B00800700 1030 929.36 90.2% 110.8%
B00800800 10130 12596.31 75.7% 80.4%
B00800900 1710 3086 19.5% 55.4%

SUMMARY 12870 16611.67 70.9% 77.4%

What I want to happen is the user can change the value of [histfcst] (on the form - I'd want original data to remain intact if possible), and once they did The FA and Con for that item would change as well as the summary section #'s would change accordingly.

Thanks for your help!

Lisa.

 

[hneal98]

Create a button on your form and put in the click event something like this:

[FA] = (1-((abs([histqty]-[histfcst]))/[histqty])
[Con] = ([histqty]/[histfcst])

When the button is clicked, the values should change in these two fields. Now for the Summary Section, is that based on a table or query, or where does that data come from?

 

[gwog]

hneal98 thanks for responding so quickly.

Sorry my question wasn't as clear as it could have been.

I acutally have it recalculating the item FA and Con fine. I simply entered the calculations for those fields in their control source.

Where I'm struglling is in the summary section. The summary has calculated fields on the form:

sumhistqty = sum([histqty])
sumhistfcst = sum([histfcst])
sumafa = 1-((abs([sumhistqty]-[sumhistfcst])/[sumhistqty])
sumcon = [sumhistqty]/[sumhistfcst]

any suggestions?

Thanks.

 

[fredtallica]

Two ideas:

1. Copy original table to a "play" table and use the play data for your scenerio form.

2. Create unbound fields and populate via a SQL statements.

Good Luck.