How to put a Calculated Value in a Field?



We have a field(s) in a table and want the contents to be a calculated
value... like...

Unit Cost times Qty = Extended Price
or List Price - (List Price * Discount) = Wholesale Price

Yes, this can be done in a Report but in this case we want to save the value
in a table field.

We have two Questons:

1 - How do I do this?

(I can calculate a value in a query like...
Total Price: [List Price]*[Qty]
but not sure how to get the value in the table field...)

2 - Once a value is calculated... like discount price... how do we keep that
price from changing later when discounts change... in other words... maybe a
customer gets a 10% discount on stuff they buy this year... but for products
they buy next year the discount may be 12%... when we change that discount
percentage next year... we don't want it effecting the prices for stuff we
sold this year.

thanks for any help.

Rick B

This is asked all the time and the normal answer is don't. You say that you
know it can be calculated in a report, but you want to save it instead. Why?
You just want to?

Store the various numbers that make up that calculation in your table
(including the 'current' discount) and then you can calculate it in your
queries, reports, or forms. Saving the current discount accomplishes your
second request of making sure that you can always see the price at the time
it was originally needed, even if some of the other factors change later.
If list price changes every year, you would still be able to perform your
calculation for a given date in the past because you would store the list
price at the time. No need to store current list, current discount, current
quantity *AND* extended discounted price. That is redundant. It is slow,
and it means you have to change multiple fields if you find a typo.

Stick with what all the experts have said in the past posts. Unless you can
give some very compelling reason that you have a special case.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question