How do I save a totals value from a subform to a table field

[Malcolmn]

I have a subform as part of a query that selects a number of records based on
various criteria and displays teh applicable data. I have created totals for
2 columns, which all works fine but now I want to take the result i.e. tthe
total and store it in a field in another table, how do I do it? I am still a
bit new at using access!!
Any help appreciated as I am sure there's a simple answer but as yet I can't
find it!!

 

[Douglas J. Steele]

Realistically, you don't. Calculated values rarely should be saved. As
fellow MVP John Vinson likes to say, "Storing calculated data generally
accomplishes only three things: it wastes disk space, it wastes time (a disk
fetch is much slower than almost any reasonable calculation), and it risks
data validity, since once it's stored in a table either the Total or one of
the fields that goes into the total may be changed, making the value WRONG."

 

[tina]

well, the simplest answer is: don't do it.

storing calculated values as hard data breaks normalization rules. standard
practice is to store the raw data, and calculate totals at runtime wherever
you need to see/use them. 1) storing calculated values as hard data invites
error, because if the raw data is changed, Access has no way of knowing that
the calculated value is now incorrect - and neither do you, unless you spend
a lot of time building safeguards into your database to catch and handle
EVERY situation where raw data can change, or records are added/deleted from
the dataset that goes into each calculated value. 2) storing calculated
values wastes space, because you've already stored the raw data. 3)
calculating values at runtime saves time, because most calculations will be
faster than a disk fetch (i'm channeling MVP John Vinson here).

if you google this issue in the ngs, you'll see basically the same answer to
this question time after time.

hth

 

[Malcolmn]

Tina/Doug,

OK I hear the advice BUT I would still like to know If it can be done and
how?

Without going into major detail, this is a payment system and I need to
record the payment made to a supplier. Obviously i could get them to key it
but that seems a bit pointless when it's already calculated the total due. Be
assured there are plenty of "controls" to ensure the record is not
amended/changed other than from this one place.

I may not know access but I have been in the IT business, professionally,
for 38+ years and was a pretty good systems software expert. As I am retired
I am doing this as freebee for a charity.
Regards
malcolm

 

[Douglas J. Steele]

The problem is that if you've set the ControlSource of the text box to a
calculation, you can no longer bind that control to a field in the table.
That means you have to manually set the value of the field in the form's
BeforeUpdate event.