Data summed in footer - storing it into table

[kiwihelen]

Hi, I am sure I am being THICK, but I am trying to solve a problem with a
database I have created.

I am using Access 2000 (thanks to the NHS and its inability to provide
up-to-date equipment) and have managed to create a database which will now
show in the footer of my forms the cumulative totals of the information in
the main body of the form. That is well and good - but I want to have those
cummulative totals appear onto the table that the original form has come from
- I have created columns in the table to put this data into, but can't work
out what to do next!

Of course if this is such a numpty question as to warrant ignoring can
someone suggest a book I can get on the topic of Access database building as
I have pretty much been self-taught to date

 

[Allen Browne]

kiwihelen, you do not store calculated totals in a relational database.

Doing so breaks the most basic rules of data normalization, and gives you
all sorts of headaches in trying to keep your calculated total up to date,
no matter how values are added, or deleted, or edited. It's not worth the
pain to do it that way, when Access can show you the calculated total so
easily.

If you are really determined to do it anyway, you would need to use the
AfterUpdate and AfterDelConfirm events of the subform to DSum() all the
related values directly from the subform's table (in case the subform is
filtered), assign the result to the main form's control, and force it to be
saved straight away so the user can't undo it.

And then start praying that this process never fails, that no one turns off
the confirmation (which means AfterDelConfirm would not fire), that there is
never a write conflict that causes the update to fail, and that no one ever
manipulates the database in any way other than your form (such as directly
in the table or query, or by executing an action query, or by cascading
deletes, or ...)