423,473 Members | 2,593 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,473 IT Pros & Developers. It's quick & easy.

Saving calculated field from a Form into a table

P: 1
We have a form that contains a calculated field ( the total price of restaurant order), and we want to save the value from the calculated field into it's table.

**Note:
this form is from TXN table and it has a subform as you can see from ORDER table.



Attached Images
File Type: jpg Capture1.jpg (78.5 KB, 84 views)
File Type: jpg Capture2.jpg (253.8 KB, 75 views)
File Type: jpg Capture3.jpg (105.9 KB, 77 views)
Aug 8 '18 #1
Share this Question
Share on Google+
5 Replies


zmbd
Expert Mod 5K+
P: 5,283
AlbaraaF
While this can be done, the usual recommendation is against storing calculated values.

If you need the calculation for a report then I suggest that you move your form's record source into a stored query, include the calculated value as one of the calculated field.

Looks like you are simply calculating an invoice total?
Aug 8 '18 #2

NeoPa
Expert Mod 15k+
P: 31,031
Hi.

Instead of using a formula on your Form for the calculated total, you should have a bound Control that gets updated whenever you enter or change the pertinent data on the Form. Does that make sense?
Aug 8 '18 #3

NeoPa
Expert Mod 15k+
P: 31,031
It looks like I cross-posted with Zmbd.

The points he makes about storing calculated values are worth considering. One of the frequent exceptions to this 'rule' is with invoice values, due to prices fluctuating over time and the calculated result being impossible, or sometimes just very difficult, to reproduce reliably.

It's worth making sure how this fits with your own database design. If it's possible to reproduce the calculations reliably, even at a very much later date, then it's not advisable to store the result at all.

However, if you do need to do so, my earlier advice shows you how best to do that.
Aug 8 '18 #4

zmbd
Expert Mod 5K+
P: 5,283
NeoPa,
One workaround I have done with fluctuating pricing is to store the price at time of purchase/sale. Same thing with discounts on pricing.

What do you think about something along these lines (very simplified - I haven't included shipping, taxes, fees, etc...):
[Prts]
[PK][FK_Spplyr][SpplyrPrtNum][PrtsDscrptn]

[PrtPrchs]
[PK][FK_Parts][PrcsAtPrchs][DscntAtPrchs]

[Invoice]
[PK][InvDate][FK_PrchOrdr][FK_PrtPrchs][UnitOrdrd][UntsRcvd]

The query against the "purchase order (PO#)" (or the "invoice number" should more than one PO# be allowed per invoice, something my company doesn't allow but others may) to pull the invoice together for the total pricing.
Aug 9 '18 #5

NeoPa
Expert Mod 15k+
P: 31,031
That can work Z. I only say that sometimes there are exceptions. Each exception can have alternative solutions, and yours minimises the non-normalised data, but not everyone wants to handle such complicated situations.

I think mostly, as well as warning of some of the possible problems, we should answer the question directly. Although we're often more experienced in the technical side of the subject, we rarely have the full story that the OP has to deal with.
Aug 9 '18 #6

Post your reply

Sign in to post your reply or Sign up for a free account.