Update A Field from another field in an open table

  • Thread starter Thread starter Seeking Knowledge
  • Start date Start date
S

Seeking Knowledge

I have a main table that I use called [Contracts]. There are 2 currency
fields on there, [OrgFedAmt] and [TotalFedAmt]. When a user is in another
“related†table, like [Amendment] or [Renewal], which has the similar
currency fields, [AmendFedAmt] and [RenewFedAmt].

When the user inputs data into the [AmendFedAmt] or [RenewFedAmt], I want it
to “ADD†that amount to the [TotalFedAmt] in the main table.

I have tried several things like: [Contracts].[TotalFedAmt] =
[Contracts].[TotalFedAmt]+[Renewal].[RenewFedAmt], but I keep getting error
messages.

What is the correct way to update a field in another table from the current
open table?
 
When the user inputs data into the [AmendFedAmt] or [RenewFedAmt], I want it
to “ADD” that amount to the [TotalFedAmt] in the main table.
Click to expand...

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 in the control source of a Form or a Report
textbox.
 
I thought that would be the answer, and I certainly appreciate your time on
this. I tried this but my tables seem to be greater than what I can think of
for a calculated field in the "main" table.

I maintain 4 different tables keeping track of contract numbers and related
Amendments, Supplements, Extensions and Renewals. They all maintain a unique
date and dollar amount that was added, i.e.

Amendment#1, Contract#20, $2,000
Amendment#2, Contract#20, $3,000
Amendment#1, Contract#44, $15,000
Amendment#2, Contract#44, $3,000

Renewal #1, Contract#20, $4,000
Renewal #2, Contract#20, $3,000
Renewal #1, Contract#44, $10,000
Renewal #2, Contract#44, $5,000

I need to maintain the above tables seperately for viewing purposes and user
input, but... when the user ADDS the above dollar amounts, I want it also to
be added to the MAIN Contracts table. I can't figure out how to come up with
a query of some sort that will look at 4 different tables, with similar
contract numbers but have different array numbers and give me an updated
total?

So, that is why I thought that an update calculation to the main table woud
be programatically easy each time the user created a new Amendment, Renewal,
etc... using the same Contract# field.

Is my thinking still incorrect? I appreciate your expertise on this?

--
Thanks for the help.


John W. Vinson said:
When the user inputs data into the [AmendFedAmt] or [RenewFedAmt], I want it
to “ADD†that amount to the [TotalFedAmt] in the main table.
Click to expand...

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 in the control source of a Form or a Report
textbox.
Click to expand...
 
I thought that would be the answer, and I certainly appreciate your time on
this. I tried this but my tables seem to be greater than what I can think of
for a calculated field in the "main" table.
Click to expand...

Don't PUT a calculated field in the "main" table - or in any other table.

Calculations are not *done* in tables! They should be done, on the fly, on
demand, as needed, in Queries or on Forms or Reports.
I maintain 4 different tables keeping track of contract numbers and related
Amendments, Supplements, Extensions and Renewals. They all maintain a unique
date and dollar amount that was added, i.e.

Amendment#1, Contract#20, $2,000
Amendment#2, Contract#20, $3,000
Amendment#1, Contract#44, $15,000
Amendment#2, Contract#44, $3,000

Renewal #1, Contract#20, $4,000
Renewal #2, Contract#20, $3,000
Renewal #1, Contract#44, $10,000
Renewal #2, Contract#44, $5,000

I need to maintain the above tables seperately for viewing purposes and user
input, but... when the user ADDS the above dollar amounts, I want it also to
be added to the MAIN Contracts table. I can't figure out how to come up with
a query of some sort that will look at 4 different tables, with similar
contract numbers but have different array numbers and give me an updated
total?
Click to expand...

Perfectly straightforward with appropriate queries; you'ld create Totals
queries on each of the four related tables, and join those four queries to
your main table in a query.

Or, you could use a Form with four Subforms (or a Report with four Subreports)
and dynamically calculate the subtotals on the Form.

Or, you could (and probably should!!!) combine your four tables into one, with
an additional field indicating which type of record (amendment, supplement,
etc.) this record represents; this will make your totals queries much easier.
So, that is why I thought that an update calculation to the main table woud
be programatically easy each time the user created a new Amendment, Renewal,
etc... using the same Contract# field.

Is my thinking still incorrect? I appreciate your expertise on this?
Click to expand...

If you are relying on opening the main table and using the table datasheet to
view or edit data... you *are* on the wrong track. Tables are for data
storage; they should NOT be used for viewing or editing data directly. That
should be done on Forms, based on appropriate Queries. Tables should be kept
"under the hood" out of sight.
 
Let me think about some of your ideas and experiment a little. I will get
back with you. I am using Forms only for user input and display, but thought
that if the "total dollars" field in the Main table was updated
programatically, it would always be correct on the form. Plus, I was having
trouble making a calculation happen every time the user added or edited a
relate subordinate table.

I have tried a totals queries on the individual subordinate tables and got a
total for each contract, but then I couldn't figure out how to combine all 4
query totals into one total for a particular contract. I was wondering if I
need to implement a "Join Table" of some sort or a Crosstab Query? Let me
kick this around some more. Please feel free to add any other comments.
Thanks!!
 
Back
Top