Simple question, but DIRE NEED!! (1 Viewer)

[camaroltd82]

I have a simple question that could make or break my database, Is it possible to have auto calculations in a table? And if so, how do I do it?

Just like in excel:

Unit Cost Current In stock Total Value
$12 4 =(Unit cost*Current)
In this case: 48

I have figured out how to do this in my enter forms, but I don't understand how those changes can be shown in the table itself, where it actually matters. Please help!!

Mike Fischer

 

[Newman]

Don't put it in tables!
NEVER put a calculated field in a table.
Newman

 

[jfgambit]

I have in the past needed to store calculations in tables so that others using the tables understand or can review the calculations. But it has been stored as a text field. You can then link the field into queries and forms and complete the necessary calculations you require.

You need to add an additional field to your table UnitCostInStock and create an update query that updates the new field to the final calculation.

See attached

 

[Pat Hartman]

Rather than using the table as the recordsource for a form or report, use a query. You can do the calculations in the query and you never need to store them.

Storing calculated fields violates the principles of good database design and leaves your app open to errors when the calculated values are out of date because someone updated one of the participating fields but did not re-do the calculation.