Adding Caluclated Field to table

[Guest]

Hi,
Can anyone tell me how to add a calculated field to a table?
I have a table called Invocies in which I need three seperate fields for
cost - SubTotal, VAT & Total.
The SubTotal value will be entered and from ths the VAT should be calculated
at 17.5%, and then this added to the SubTotal to give the total.
I have managed to do this in a form but I need the values to be stored in a
table.
Can someone please explain how this can be done?
I read somewhere that a query could be used but coulnd't understand it so
any further explanation of this would be appreciated,

Thanks

 

[Guest]

It is the wrong approach to save a calculated field in a table,
In the form or report you can always display a calculated field as you do now.

The only thing you can get by saving a calculated fields is errors.
e.g - a user will enter the table and change values in it, how can you
change the calculated field.
By doing so you will have more maintnance to your DB.

 

[tina]

first, you must create a field in the table to hold each value. i'm guessing
that you're using calculated controls in your form to *display* the values.
make sure that the appropriate table fields are included in your form's
RecordSource, then you can use VBA code or SetValue macro actions to enter
the calculated values into the fields.

you can also use an Update query to enter the calculated values into your
table.

there are other ways to save the calculated values into a table, but the
above are pretty easy and straightforward. exactly how you choose to do it
will depend on your skill level in Access, and the circumstances surrounding
the update.

hopefully, you have a solid business reason to save calculated values in a
table, since it is a departure from standard database design rules.

hth

 

[fredg]

Hi,
Can anyone tell me how to add a calculated field to a table?
I have a table called Invocies in which I need three seperate fields for
cost - SubTotal, VAT & Total.
The SubTotal value will be entered and from ths the VAT should be calculated
at 17.5%, and then this added to the SubTotal to give the total.
I have managed to do this in a form but I need the values to be stored in a
table.
Can someone please explain how this can be done?
I read somewhere that a query could be used but coulnd't understand it so
any further explanation of this would be appreciated,

Thanks

Regarding >but I need the values to be stored in a table. <

No you don't.

How are you arriving at the SubTotal amount. Is this manually entered
as one figure?
If so, all you need do is store that [SubTotal] and the [VAT] rate.
In an unbound control, use:
=[SubTotal] * [VAT]

This result should not be stored. Whenever you need the result, in a
query, on a form, or in a report, simply recalculate it, as above.

It need not be stored.