Calculated Form field data not store in table (1 Viewer)

[Bandara]

I have create form.

QTY
Unit Price
Total

I create a query to generate the total in the form Total field. It generates but when I save the Total is not stored to the table. Please guide me to resolve this step by step. I don't much expert in access

Thank You

 

[June7]

Ideally, this calculated value should not be stored in table, it should be calculated when needed. You could use a Calculated field in table design or calculate in query or in textbox on form/report.

Saving calculated value from form to table would require code (macro or VBA).

 

[arnelgp]

you can also add a Calculated Column to your table that calculates [qty]*[unit price]

 

[oleronesoftwares]

Just to add to the suggestions from @arnelgp and @June7

In VBA Code
under the after update event of qty
me.total= me.qty *me.unitprice


also
under the after update event of unitprice
me.total= me.qty *me.unitprice


this will ensure that the total value is stored in the database

 

[oleronesoftwares]

If you are not that comfortable with VBA, you can have an update query run to perform the calculation and store the value.

in this sample images i have a table called calculate.

 

[June7]

Or avoid all this complication and as already advised, don't save the calculated value.

 

[oleronesoftwares]

Or avoid all this complication and as already advised, don't save the calculated value

Calculated values are sometimes expected to be saved especially when working with financial applications, e.g Payroll, Loan processing, mortgage, general ledger etc.

The after update event of the control or even the command button click event can be used to trigger the calculation of such values, so one does not have to perform it in the control source of the control.

 

[plog]

Listen to June--do not store the total. Nor use a calculated field in a table.

You obtain the total on the form via simple math and then if needed elsewhere you use a query to calculate it.

 

[oleronesoftwares]

Calculated field data type

Above is the official documentation from Microsoft, there is a calculated field data type in tables

You can also read the article below

Calculated Data Type in Microsoft Access 2010 | Calculated Data Type in Microsoft Access 2010 | InformIT

This article introduces the new Calculated Field data type added to Access 2010. It discusses how they work and shows how to create them.

www.informit.com

 

[Minty]

There are some issues with calculated fields, they have been available for years in SQL server. I frequently use them to create a FullName field from a First and Last Name to save me concatenating them in a query.
They are fantastic when the data is pretty static and not used extensively in queries, however some things you should be aware of

1. No indexes, so any queries on that field can't become efficient.
2. They often seen as a strange datatype - so joins on them either don't work or are flakey at best.

More here http://allenbrowne.com/casu-14.html

 

[oleronesoftwares]

@Minty thank you for sharing the link http://allenbrowne.com/casu-14.html

The writer suggests using the after update event to store the results which is what i suggested in post 4 of this thread.

 

[June7]

Allen does say it is best not store calculated value but if you must ...

And, yes, there are circumstances justifying saving a calculated value, however, not in Bandara's case.

 

[Minty]

@Minty thank you for sharing the link http://allenbrowne.com/casu-14.html

The writer suggests using the after update event to store the results which is what i suggested in post 4 of this thread.

As per everyone else's advice, you shouldn't store a calculated result, unless something in the calculation might change over time.
Even then, it's generally good practice to store the period and value that the calcuation is based on, as it will allow for much easy error correction later on.

 

[oleronesoftwares]

Lets look at the confusion here

When you are using the after update event you are storing a calculated result that is different from a calculated field.

Most enterprise financial applications follow this rule.

When you make a withdrawal at an atm and your balance is sent via sms immediately, its the calculated result that is sent, not the calculated field.

How many here has tried sending a query based on calculated field as an sms, before?


So allen discourages calculated field, but not calculated result, the only concern according to is article is the user can change the value of one of the variables, this fear also exists when you use a select statement to create calculated field.

in allen's article he has a session called

You want to store a calculated result anyway?​

​

 

[oleronesoftwares]

As per everyone else's advice, you shouldn't store a calculated result, unless something in the calculation might change over time.
Even then, it's generally good practice to store the period and value that the calcuation is based on, as it will allow for much easy error correction later on

That is the reason applications have audit trails in them, to show who changed what. Calculated results exists has a value not expression in the table.

I get the idea that normalization must be followed, but you can still store calculated results without breaking normalisation rules

DBMS Normalization: 1NF, 2NF, 3NF and BCNF with Examples - javatpoint

DBMS Normalization with DBMS Overview, DBMS vs Files System, DBMS Architecture, Three schema Architecture, DBMS Language, DBMS Keys, DBMS Generalization, DBMS Specialization, Relational Model concept, SQL Introduction, Advantage of SQL, DBMS Normalization, Functional Dependency, DBMS Schedule...

www.javatpoint.com

 

[Minty]

I'm not sure of the point you are trying to make.
We all agree that if a calculation is dependent on something that might change over time - Let's use a VAT rate and the calculated VAT amount as a classic example - you should either store the VAT amount, or store the VAT rates with the valid time periods to enable accurate recalculation.

But if you are simply calculating a value e.g. Qty * Price for a Total, you shouldn't store the total, as if either the Qty or Price value is changed the result is now wrong, and you have to capture any and all changes to those initial values to ensure your Total gets updated. This is a massive waste of your effort.

 

[June7]

Allen does advise saving is not preferred - "Never store a value that is dependent on other fields" (although he does recognize exceptions http://allenbrowne.com/AppInventory.html). Then he goes on against the use of Calculated type field in table as a separate advisory.

Allen's example of a case for saving calculated result may not be the best. Could instead have a yes/no field HasFee then take that field into consideration in the calculation: =Me.Quantity * Me.UnitPrice * IIf(HasFee, 0.1, 0) - again, no need to save calculated result.

I think we all know saving a calculated result is not the same as a Calculated field in table. I don't see any confusion. The point is, saving calculated result is not justified in this case.

No experience with sending SMS and not sure what bearing it has on this case.

 

[oleronesoftwares]

Qty * Price for a Total, you shouldn't store the total, as if either the Qty or Price value is changed the result is now wrong

That is the point am making, if either the qty or price changes, automatically the result also changes to reflect the alteration.

As long as the code is on the after update event of both qty and price, then any alteration to either qty and price is reflected.


I am talking from real life scenarios, i have been priviledged to develop various financial applications because my first degree is in accounting, so this process i have tested overtime.

 

[oleronesoftwares]

So here is what i wanted to share initially before i had to respond to @Minty's post

https://www.oracle.com/technetwork/database/database-technologies/rdb/automatic-columns-132042.pdf

Please read the article from oracle, they have calculated fields that store values in rows of the table, and this i think was introduced in 1984, so am surprised in 2021 developers are still wary of such.

 

[Minty]

That is the point am making, if either the qty or price changes, automatically the result also changes to reflect the alteration.

As long as the code is on the after update event of both qty and price, then any alteration to either qty and price is reflected.


I am talking from real life scenarios, i have been priviledged to develop various financial applications because my first degree is in accounting, so this process i have tested overtime.

You have just proved my point - you have to capture the updates and apply an update through code.
If you only ever calculate the result this is unnecessary.

I too have worked with many accountancy applications, and nearly always at some point, the results get out of sync, and you have to apply an adjustment. This is because most accountancy packages are built around standard accountancy practices, which is why most of them are unnormalised in their structure. Accountants aren't database people.

IMHO, Double-entry bookkeeping practices have no place in a modern computerised world but is still the way to go if you are an accountant.