Have Fields Automatically Calculate
in [Getting Started]
|
Prev: Vista
Next: Compact and Repair
From: NickW on 10 Jul 2008 22:35 I would like to have a field add numbers from two other fields. so Total= subtotal1+subtotal2 I want this done at the table level. I was able to acheive this at the form, but then it wasn't writing the value to the table.
From: Arvin Meyer [MVP] on 10 Jul 2008 23:40 Since a query, form, or report can re-calculate this at any time, there is no reason to store the calculated value. It is also a violation of database normalization rules. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "NickW" <NickW(a)discussions.microsoft.com> wrote in message news:5E14899C-9671-4E56-BF65-61BC807F48FD(a)microsoft.com... >I would like to have a field add numbers from two other fields. so > Total= subtotal1+subtotal2 > I want this done at the table level. I was able to acheive this at the > form, but then it wasn't writing the value to the table.
From: John Spencer on 11 Jul 2008 07:34 Plus Access does not support this at the table level. If you really feel you HAVE to do this, you will have to do it at the data entry level of a form. If someone ever modifies the component parts using a query or direct entry into a table (or a query) the total will be inaccurate. So Arvin Meyer's suggestion of recalculating the total when needed is the correct way to handle this. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Arvin Meyer [MVP] wrote: > Since a query, form, or report can re-calculate this at any time, there is > no reason to store the calculated value. It is also a violation of database > normalization rules.
From: NickW on 11 Jul 2008 10:05 Okay. Well, i was able to get the data to show in a text box by having the control source be the sum of two fields, but then how do I write this to the table? When I went into the table, there was no data in the product field "John Spencer" wrote: > Plus Access does not support this at the table level. If you really feel you > HAVE to do this, you will have to do it at the data entry level of a form. > > If someone ever modifies the component parts using a query or direct entry > into a table (or a query) the total will be inaccurate. So Arvin Meyer's > suggestion of recalculating the total when needed is the correct way to handle > this. > > John Spencer > Access MVP 2002-2005, 2007-2008 > The Hilltop Institute > University of Maryland Baltimore County > > Arvin Meyer [MVP] wrote: > > Since a query, form, or report can re-calculate this at any time, there is > > no reason to store the calculated value. It is also a violation of database > > normalization rules. >
From: John Spencer on 11 Jul 2008 11:52
Again, I stress that you should not record the total in the table at all. But, since you seem determined to do so, REMOVE the formula from the textbox. Set the textbox control source to the name of the field you want to store the amount. On the two controls that contain the information you are adding together, use the after update event and add code that looks something like the following. Private Sub tAmount1_AfterUpdate() Me.someTotalControl = Me.tAmount1 + Me.tAmount2 End Sub Private Sub tAmount2_AfterUpdate() Me.someTotalControl = Me.tAmount1 + Me.tAmount2 End Sub John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County NickW wrote: > Okay. Well, i was able to get the data to show in a text box by having the > control source be the sum of two fields, but then how do I write this to the > table? When I went into the table, there was no data in the product field > > "John Spencer" wrote: > >> Plus Access does not support this at the table level. If you really feel you >> HAVE to do this, you will have to do it at the data entry level of a form. >> >> If someone ever modifies the component parts using a query or direct entry >> into a table (or a query) the total will be inaccurate. So Arvin Meyer's >> suggestion of recalculating the total when needed is the correct way to handle >> this. >> >> John Spencer >> Access MVP 2002-2005, 2007-2008 >> The Hilltop Institute >> University of Maryland Baltimore County >> >> Arvin Meyer [MVP] wrote: >>> Since a query, form, or report can re-calculate this at any time, there is >>> no reason to store the calculated value. It is also a violation of database >>> normalization rules. |