Field1 equals (Field2 * Field3 - Field4)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everybody,

I am having trouble designing a table/query that will do the following.

The table will have a number of fields called Field1, Field2, Field3, Field4
etc...

Some of these fields will be update by the user via a form. For example, the
user may enter "50" into Field1 and "100" into Field2. I want Field3 to be
the sum of Field1 and Field 2 (ie: Field 3 = Field 1 + Field 2).

I know that there is a very simple way to do this but I cannot figure it out!

Help!

Thanks
Regards
Greg
 
Hi everybody,

I am having trouble designing a table/query that will do the following.

The table will have a number of fields called Field1, Field2, Field3, Field4
etc...

Some of these fields will be update by the user via a form. For example, the
user may enter "50" into Field1 and "100" into Field2. I want Field3 to be
the sum of Field1 and Field 2 (ie: Field 3 = Field 1 + Field 2).

I know that there is a very simple way to do this but I cannot figure it out!
Click to expand...

There are two ways, both fairly simple. NEITHER involves storing the
calculated value - it is almost never necessary to do so.

Either put a textbox on the Form, label it Field3 (or, much better, something
meaningful to your users), and set its Control Source to

=[Field1] + [Field2]

It will automatically calculate the sum for the current record.

Or, create a Query based on your table (which should *not* include Field3);
include Field1 and Field2, and in a vacant Field cell in the query grid type

Field3: [Field1] + [Field2]

You can then reference Field3 on your form (but you cannot edit that value,
since it depends on the other two fields).


John W. Vinson [MVP]
 
Thanks John

I tried this method and was finding that when I moved to a new record the
calculated value in the TEXT box remained the same...

Regards
Greg

John W. Vinson said:
Hi everybody,

I am having trouble designing a table/query that will do the following.

The table will have a number of fields called Field1, Field2, Field3, Field4
etc...

Some of these fields will be update by the user via a form. For example, the
user may enter "50" into Field1 and "100" into Field2. I want Field3 to be
the sum of Field1 and Field 2 (ie: Field 3 = Field 1 + Field 2).

I know that there is a very simple way to do this but I cannot figure it out!
Click to expand...

There are two ways, both fairly simple. NEITHER involves storing the
calculated value - it is almost never necessary to do so.

Either put a textbox on the Form, label it Field3 (or, much better, something
meaningful to your users), and set its Control Source to

=[Field1] + [Field2]

It will automatically calculate the sum for the current record.

Or, create a Query based on your table (which should *not* include Field3);
include Field1 and Field2, and in a vacant Field cell in the query grid type

Field3: [Field1] + [Field2]

You can then reference Field3 on your form (but you cannot edit that value,
since it depends on the other two fields).


John W. Vinson [MVP]
Click to expand...
 
Thanks John

I tried this method and was finding that when I moved to a new record the
calculated value in the TEXT box remained the same...

Regards
Greg

John W. Vinson said:
Hi everybody,

I am having trouble designing a table/query that will do the following.

The table will have a number of fields called Field1, Field2, Field3, Field4
etc...

Some of these fields will be update by the user via a form. For example, the
user may enter "50" into Field1 and "100" into Field2. I want Field3 to be
the sum of Field1 and Field 2 (ie: Field 3 = Field 1 + Field 2).

I know that there is a very simple way to do this but I cannot figure it out!
Click to expand...

There are two ways, both fairly simple. NEITHER involves storing the
calculated value - it is almost never necessary to do so.

Either put a textbox on the Form, label it Field3 (or, much better, something
meaningful to your users), and set its Control Source to

=[Field1] + [Field2]

It will automatically calculate the sum for the current record.

Or, create a Query based on your table (which should *not* include Field3);
include Field1 and Field2, and in a vacant Field cell in the query grid type

Field3: [Field1] + [Field2]

You can then reference Field3 on your form (but you cannot edit that value,
since it depends on the other two fields).

John W. Vinson [MVP]
Click to expand...
Click to expand...

It won't stay the same if [Field1] and [Field2] are bound to fields in
your table. It's only [Field3] that should not be bound to a field in
your table.
 
Hi John,

I will try this and check everything again and let you know the result.

Thanks again
Regards
Greg

fredg said:
Thanks John

I tried this method and was finding that when I moved to a new record the
calculated value in the TEXT box remained the same...

Regards
Greg

John W. Vinson said:
On Fri, 16 Mar 2007 21:26:08 -0700, Lateral

Hi everybody,

I am having trouble designing a table/query that will do the following.

The table will have a number of fields called Field1, Field2, Field3, Field4
etc...

Some of these fields will be update by the user via a form. For example, the
user may enter "50" into Field1 and "100" into Field2. I want Field3 to be
the sum of Field1 and Field 2 (ie: Field 3 = Field 1 + Field 2).

I know that there is a very simple way to do this but I cannot figure it out!

There are two ways, both fairly simple. NEITHER involves storing the
calculated value - it is almost never necessary to do so.

Either put a textbox on the Form, label it Field3 (or, much better, something
meaningful to your users), and set its Control Source to

=[Field1] + [Field2]

It will automatically calculate the sum for the current record.

Or, create a Query based on your table (which should *not* include Field3);
include Field1 and Field2, and in a vacant Field cell in the query grid type

Field3: [Field1] + [Field2]

You can then reference Field3 on your form (but you cannot edit that value,
since it depends on the other two fields).

John W. Vinson [MVP]
Click to expand...
Click to expand...

It won't stay the same if [Field1] and [Field2] are bound to fields in
your table. It's only [Field3] that should not be bound to a field in
your table.
Click to expand...
 
Hi John,

Just to let you know that I implemented your first recommendation and it
works fine.....I am not sure what I was doing incorrectly before.

I have another question.

I have created Field3 as a Text box (unbound) on the form. If I want to
permanently save the calculated value in Field3 into an actual field in a
table how would I do this?

The reason I want to do this is that I am going to have a form that contains
a number of fields (10) that the user will enter values into. The form will
perform various calculations like a simple spreadsheet. When the user is
happy with the values that they have entered and the calculations, they can
then "save" the information into a record in a table so that they can then
use the form each month to enter new values and save the information into
another record in the table. This will enable them to have a single record
for each month .

Regards
Greg

Lateral said:
Hi John,

I will try this and check everything again and let you know the result.

Thanks again
Regards
Greg

fredg said:
Thanks John

I tried this method and was finding that when I moved to a new record the
calculated value in the TEXT box remained the same...

Regards
Greg

:

On Fri, 16 Mar 2007 21:26:08 -0700, Lateral

Hi everybody,

I am having trouble designing a table/query that will do the following.

The table will have a number of fields called Field1, Field2, Field3, Field4
etc...

Some of these fields will be update by the user via a form. For example, the
user may enter "50" into Field1 and "100" into Field2. I want Field3 to be
the sum of Field1 and Field 2 (ie: Field 3 = Field 1 + Field 2).

I know that there is a very simple way to do this but I cannot figure it out!

There are two ways, both fairly simple. NEITHER involves storing the
calculated value - it is almost never necessary to do so.

Either put a textbox on the Form, label it Field3 (or, much better, something
meaningful to your users), and set its Control Source to

=[Field1] + [Field2]

It will automatically calculate the sum for the current record.

Or, create a Query based on your table (which should *not* include Field3);
include Field1 and Field2, and in a vacant Field cell in the query grid type

Field3: [Field1] + [Field2]

You can then reference Field3 on your form (but you cannot edit that value,
since it depends on the other two fields).

John W. Vinson [MVP]
Click to expand...

It won't stay the same if [Field1] and [Field2] are bound to fields in
your table. It's only [Field3] that should not be bound to a field in
your table.
Click to expand...
Click to expand...
 
there's rarely a good business or technical reason to break normalization
rules by saving calculated values into a table. just save the raw data in
one or more tables, as appropriate - when the user is happy with the values
entered, and the calculations displayed, on the data entry form - then you
can use that raw data to calculate whatever values you want, whenever and
wherever you want - query, form, report.

hth


Lateral said:
Hi John,

Just to let you know that I implemented your first recommendation and it
works fine.....I am not sure what I was doing incorrectly before.

I have another question.

I have created Field3 as a Text box (unbound) on the form. If I want to
permanently save the calculated value in Field3 into an actual field in a
table how would I do this?

The reason I want to do this is that I am going to have a form that contains
a number of fields (10) that the user will enter values into. The form will
perform various calculations like a simple spreadsheet. When the user is
happy with the values that they have entered and the calculations, they can
then "save" the information into a record in a table so that they can then
use the form each month to enter new values and save the information into
another record in the table. This will enable them to have a single record
for each month .

Regards
Greg

Lateral said:
Hi John,

I will try this and check everything again and let you know the result.

Thanks again
Regards
Greg

fredg said:
On Fri, 16 Mar 2007 22:49:00 -0700, Lateral wrote:

Thanks John

I tried this method and was finding that when I moved to a new record the
calculated value in the TEXT box remained the same...

Regards
Greg

:

On Fri, 16 Mar 2007 21:26:08 -0700, Lateral

Hi everybody,

I am having trouble designing a table/query that will do the following.

The table will have a number of fields called Field1, Field2, Field3, Field4
etc...

Some of these fields will be update by the user via a form. For example, the
user may enter "50" into Field1 and "100" into Field2. I want Field3 to be
the sum of Field1 and Field 2 (ie: Field 3 = Field 1 + Field 2).

I know that there is a very simple way to do this but I cannot figure it out!

There are two ways, both fairly simple. NEITHER involves storing the
calculated value - it is almost never necessary to do so.

Either put a textbox on the Form, label it Field3 (or, much better, something
meaningful to your users), and set its Control Source to

=[Field1] + [Field2]

It will automatically calculate the sum for the current record.

Or, create a Query based on your table (which should *not* include Field3);
include Field1 and Field2, and in a vacant Field cell in the query grid type

Field3: [Field1] + [Field2]

You can then reference Field3 on your form (but you cannot edit that value,
since it depends on the other two fields).

John W. Vinson [MVP]


It won't stay the same if [Field1] and [Field2] are bound to fields in
your table. It's only [Field3] that should not be bound to a field in
your table.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top