Doing Math In Access Tables

[Guest]

I am creating an inventory database for work. I want one field in the table
to hold the sum of data in two other fields in the same table (i.e. Total
Price = Unit Price X Qty). Is it possible to store the result of the math in
the table itself along OR do I have to do the calculation in the report only?

Experience: beginner

 

[Guest]

You definitly shouldn't store a calculation field in a table, it mean that
you'll have to maintain it, and it can cause only mistake.
You can always create a query that will return this value
Select [Unit Price],Qty, [Unit Price] * Qty As Total_Price From TableName

or make a field in a report return the value, in the control source of the
field you can write
=[Unit Price] * [Qty]

 

[Guest]

Thanks for the response. It did help.

Just curious though. Why is it not ok to store the result of the calculation
of two fields in a table in another field in that table?

You definitly shouldn't store a calculation field in a table, it mean that
you'll have to maintain it, and it can cause only mistake.
You can always create a query that will return this value
Select [Unit Price],Qty, [Unit Price] * Qty As Total_Price From TableName

or make a field in a report return the value, in the control source of the
field you can write
=[Unit Price] * [Qty]
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I am creating an inventory database for work. I want one field in the table
to hold the sum of data in two other fields in the same table (i.e. Total
Price = Unit Price X Qty). Is it possible to store the result of the math in
the table itself along OR do I have to do the calculation in the report only?

Experience: beginner

 

[Guest]

Because you need to maintain it, an automatic update can't be perform
directly in the table, you will have to use either a query or a form to do
that.
So if a user will update one of the fields [qty] or [price], it mean that he
has to remembr updating the total field.
Keeping the calculated field in the table mean that you have to count on
people to maintain it.

But a query behave just as a table, when the total will be calculated every
time you run the query, so you will never have any error.
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

Thanks for the response. It did help.

Just curious though. Why is it not ok to store the result of the calculation
of two fields in a table in another field in that table?

You definitly shouldn't store a calculation field in a table, it mean that
you'll have to maintain it, and it can cause only mistake.
You can always create a query that will return this value
Select [Unit Price],Qty, [Unit Price] * Qty As Total_Price From TableName

or make a field in a report return the value, in the control source of the
field you can write
=[Unit Price] * [Qty]
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I am creating an inventory database for work. I want one field in the table
to hold the sum of data in two other fields in the same table (i.e. Total
Price = Unit Price X Qty). Is it possible to store the result of the math in
the table itself along OR do I have to do the calculation in the report only?

Experience: beginner

 

[Rick Brandt]

Thanks for the response. It did help.

Just curious though. Why is it not ok to store the result of the
calculation of two fields in a table in another field in that table?

If you were using Excel and you wanted column C to be the result of column A
plus column B would it be a better idea to enter =[A1]+[B1] into cell [C1] and
then copy that expression down the column or would it be better to write a macro
that walks down the sheet performing the calculation and storing the result in
column C?

The former correlates to doing the Access calculation in a query and the latter
correlates to performing the calculation in a form and then stuffing the result
into another field of the table.

 

[Douglas J. Steele]

Fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the response. It did help.

Just curious though. Why is it not ok to store the result of the
calculation
of two fields in a table in another field in that table?

You definitly shouldn't store a calculation field in a table, it mean
that
you'll have to maintain it, and it can cause only mistake.
You can always create a query that will return this value
Select [Unit Price],Qty, [Unit Price] * Qty As Total_Price From TableName

or make a field in a report return the value, in the control source of
the
field you can write
=[Unit Price] * [Qty]
--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.

Good luck

I am creating an inventory database for work. I want one field in the
table
to hold the sum of data in two other fields in the same table (i.e.
Total
Price = Unit Price X Qty). Is it possible to store the result of the
math in
the table itself along OR do I have to do the calculation in the report
only?

Experience: beginner