how do i store calculated fields into a table

[John thomas]

Hi all
can any one explain to me how to store a calculated field into a table
I know you are going to say dont .But i realy do need to
I am preparing an invoive and , I have a field called deposit .Now this field calculates %40 of the total of the order .but if the user wishes they can enter what they like .Now this field must be stored in a table for ever and a day and must not change or the invoic will be useles .I can see no other way of doing this and my research tells me (allen Brown) that this is sometimes necesary
Allen brown explains how to this ,but it totally loses me .Can any one give me an easy explanation of how to do it rather than telling me not to do it

Many thanks john

 

[Uncle Gizmo]

Regarding Allen's instructions, explain in terms of them where you are stuck. Would be a good idea to post a link ... So we are all singing off the same hymn sheet.

 

[John thomas]

Hi thanks for getting back. Been away on business hence not resonding
Gettting back to my problem of not being able to store calculated fields into a table
I have reloked at allen browns post on the internet ,he explains how to store a caculated field in a table using access 2010 ,using the calculated field in the table .
But does not explain how to sum various records .

I have an orders sub form with the records i need to sum to arive at a grand total .

Is there an expression i can use in the table that sums only the records I need ,Or is there a different way
Also there must have been away of doing this before access 2010
There seems to be vary little advise on this subject anywere

 

[gemma-the-husky]

what you want to do is not really a calculated field - a calculated field is a value that you can determine by reference to other information in your database - and you cannot calculate the deposit without either storing the deposit amount, or the deposit %age

The problem with storing both the percentage AND the amount is that it can lead to inconsistency. If you set the deposit rate at 30%, but your code does not calculate the deposit AMOUNT correctly, you get inconsistency. If you never store both values, you cannot get the inconsistency. You can always calculate the deposit, if you have the &age - OR you can calculate the %age if you have the deposit value.

Now MS Access introduced something called "a calculated field". I have no idea what it does exactly, and I would not use it, for the above reasons. I suspect that maybe it is an automatic mechanism to define a field as being the result of a calculation - thereby automatically avoiding any problems inherent in the possible inconsistency detailed above.

However, one issue with using such non-standard constructs is that you can become locked into Access. If you upsize to a different backend, these Access constructs will probably not work, and you will have to spend time fixing the problems (multi-valued fields are similar)

As far as implementation goes, you could decide to store nothing for a general case, but if a variant deposit rate is used, then store the actual deposit (or deposit %age). The problem then is that if you decide to change the standard deposit, the stored values become wrong.

I would just store the deposit value or %age as a separate field.

------
how to do it - in your order/invoice header, just have a field called deposit. On the entry form, have a control bound to this field. I would have a button to calculate the standard deposit (40% of whatever the invoice total is, but allow the user to enter whatever they want - maybe with some appropriate warning messages). Use the forms before update event to make sure the deposit has been entered.

really, whatever makes the "look and feel" work the way you want.

 

[John thomas]

THanks for your help