Writing calculated data from a form to table

[crankyfj]

I have searched for a solution to this and had many results but actually none
solved the original question. Firstly need a categoric yes or no if it is
possible then if possible, how.
Problem;
Entering data into form A goes to table A (on defocus). But also on form A
is a field which adds the numerical data from some these fields to produce a
Sum result. All I want to do is store this Sum result back into the
appropriate column in the original Table A for later use. This I beleive will
save number crunching as this table will grow into the hundreds with 5 such
calculcated results. I wish to late run queries whereby these Sum reuslts
will be the prime search target(s).

If I could Sum within the table that would be much better any ideas on this?

 

[Jeff Boyce]

If possible - yes.

Good idea - rarely.

Use a query to calculate your Sum on the fly, then use that query in forms,
reports, etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[crankyfj]

Jeff,
Thanks for the prompt response.
Just to confirm it is not as easy as I expected by my descripiton.
But you say Query is the only solution.
I will try it
Thank you once agin.

 

[John W. Vinson]

All I want to do is store this Sum result back into the
appropriate column in the original Table A for later use.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you have a DEMONSTRATED, PROVEN case where the calculation is in fact
unacceptably slow, then yes, you can use the BeforeUpdate event of the form to
"push" the calculated value from a textbox with an expression as its control
source into another textbox bound to a table field:

Me!boundcontrol = Me!calculatedcontrol

But don't assume that a modern PC cpu is going to break a sweat over summing
five - or five thousand - numbers.

 

[crankyfj]

John,
Appreciate your buying into the discussion. This is only the beginning. Once
the Sums are attained they will go thorough another series of calculations
and mathematical procedures and graphical presentation. True a modern PC
should not even break a sweat but I can not guarantee the condition of the
PC's who will use the finished product. The seed data will be historical
after the event so it will never change thus neither will the outcomes.
(Excepting corruption). I doubt I will ever reach "unacceptibly slow" but it
does afford me the ability to analyse data at the various stages of it's
generation.
However I am rethinking appending the original data table in favour of
creating a new one for that exact reason.
Thanks
PS in case you hadn't gussed I am a noob at Access.

 

[דרור]

It is possible and also makes sense. There ARE situations where you want to
keep calculated fields in the base table. For example, you calculate the
Sales Tax $ and you want to save the $ value in case that the Sales Tax %
will change in the futute.

My solution is this:

I add a TextBox to the form. Call is x_MyField. This is the calculated
field.
x_myField data source will be something like "=Sum(dblRevenues)" (not
important, just an example of a calculated field).

I add another TextBox and set the source to the base table's field where I
want to store the calculated value. Call it MyField.

I add an Form_Unload event to the form and there I do:
MyField.value = x_MyField

Dror