hi, I have a quantity field in a tools table and i want to know that if i issue a tool then system will update the quantity automatically and show me the remaining live balance? i want to create a query and then form based on this.
thanks
hi, I have a quantity field in a tools table and i want to know that if i issue a tool then system will update the quantity automatically and show me the remaining live balance? i want to create a query and then form based on this.
thanks
Is this a calculated field?
What exactly does the calculation look like?
All calculated fields are dynamic, so their value should update as the values that the formulas use change.
You should be able to confirm this with some simple testing.
By the way, you will find that many users (myself included) never use the calculated field option in tables, and instead do the calculated fields in queries. This is a fairly new option in Access, and is limited and is not compatible with any other database program (SQL, Oracle, etc). No other ones allow this, so if you ever needed to convert your database from Access to one of these other programs, that part wouldn't work.
Can we create a calculated field in a table or only in query?
Calculated Fields
How do you get Access to store the result of a calculation?
For example, if you have fields named Quantity and UnitPrice, how do you get Access to write Quantity * UnitPrice to another field called Amount?
The best answer is, "Don't!"
Calculated fields belong in queries, not tables.
Calculated columns are part of life on a spreadsheet, but do not belong in a database table. Never store a value that is dependent on other fields: it's a basic rule of normalization. Break the rule, and you have to worry about how to maintain the calculation correctly in every possible circumstance.
Ask yourself, "Would it be wrong if my field contained a value different from the calculation?" If you answer Yes, you must not store the field. If the difference would be meaningful, you do need the field.
So, how do you get the calculated field if you do not store it in a table? Use a query:
- Create a query based on your table.
- Type your expression into the Field row of the query design grid:
Amount: [Quantity] * [UnitPrice]
This creates a field named Amount. Any form or report based on this query treats the calculated field like any other, so you can easily sum the results. It is simple, efficient, and foolproof.
...
Even more serious, the calculated results (in a table) are not reliable. If you change the Expression after data has been entered into the table, the existing results may not be updated correctly (through new records are edits are updated), so you cannot rely on the results. A compact/repair does not recalculate, so there is no obvious way to repair the bad results.
Since you cannot index calculated fields anyway, there is no performance benefit to be gained from using them. You are violating fundamental rules of data normalization for no good purpose. We recommend you use queries for calculated fields, just as in previous versions.
http://allenbrowne.com/casu-14.html