syntax help automatic transfer of data in calc field to permanent

[bird lover]

msaccess 2003:
query PastDue is based on joins of several queries including query
qinvestment.

Query PastDue has calculated field called [qtotal due] which is based on
permanent fields [arrears] and [new bill]. qtotal: [arrears] + [new bill]

Form ClientAccount is based on QueryPastDue.

Question1: On form At properties category open form, what is the syntax so
that if permanent field TotalDue does not equal qtotal, then qtotal equals
TotalDue.

Q2: Is there a way whenever QueryPastDue is used, then the same update
program works

I am not a programmer nor professional in this area. Simplicity if possible
would be appreciated.

 

[John Vinson]

Question1: On form At properties category open form, what is the syntax so
that if permanent field TotalDue does not equal qtotal, then qtotal equals
TotalDue.

The first answer would be - don't save it AT ALL.

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.

Do you have some compelling reason to violate this principle?

John W. Vinson[MVP]

 

[bird lover]

Yes. I do have a compelling reason relating to Microsoft Word's inability to
merge into complex databases with dozens of queries and tables, all linked by
dozens of linked databases. See below.

I use field qtotal in a merge with microsoft word. The original query is a
comibinatino of many linked tables from other databases. Word cannot find
the original query, probably because of I have so many queries in the master
database. However, it can find the field in a simple database. The bottom
line is it's easier just to put the data into just another field on a one
table, one query database. Actually, I have about fifteen similar calculated
fields, I want to do this with, each with different joins from other linked
databases.

Any help would be greatly apprecaited.