If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update A Field from another field in an open table
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? -- Thanks for the help. |
#2
|
|||
|
|||
Update A Field from another field in an open table
On Sun, 11 Oct 2009 17:49:01 -0700, Seeking Knowledge wrote: When the user inputs data into the [AmendFedAmt] or [RenewFedAmt], I want it to ADD that amount to the [TotalFedAmt] in the main table. 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. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Update A Field from another field in an open table
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" wrote: On Sun, 11 Oct 2009 17:49:01 -0700, Seeking Knowledge wrote: When the user inputs data into the [AmendFedAmt] or [RenewFedAmt], I want it to “ADD” that amount to the [TotalFedAmt] in the main table. 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. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Update A Field from another field in an open table
On Mon, 12 Oct 2009 06:56:02 -0700, Seeking Knowledge
wrote: 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. 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? 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? 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. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Update A Field from another field in an open table
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!! -- Thanks for the help. "John W. Vinson" wrote: On Mon, 12 Oct 2009 06:56:02 -0700, Seeking Knowledge wrote: 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. 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? 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? 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|