Storing results of calculated query

[alexlds]

Ive hit an (as always) unexpected problem

How do you store in a table the RESULT of a calculated field??

When I create records with a form, some of the fields are calculated in the query grid on which the form is based. What I need to do is store the result of the calculation (a snapshot???) in a table when the record is first saved.

I suspect that this may be something to do with crosstab queries which I have never used before.

A simple example would be a great help to me.

Lets say I have a value of 25 in a field. In the query grid I have a calculated field - say 5 x the 25. At the moment when I save the record it saves the 25 - but what I need is to make a record of the result of 125 produced at the time that the record was first saved. As it is, if the 5 is subsequently altered / edited to a different, then value the original calculation result is lost.

Im really hoping there is a simple way of doing this - if anyone can help?? Im not especially looking to go down the snapshot or crosstab route (if either of those are appropriate) unless thats the simplest way of doing it.

 

[MStef]

You don't need to save the calculated fields, because
you can calculate it again when you need it.

 

[John Big Booty]

In all but a very few, and special circumstances, it is considered bad practice to store the results of calculated fields. If you can calculate the field now, you can calculate it at any other time that you need to display it in a Form or Report.

The problem with storing calculated fields in Access is that unlike Excel if one of the underlying values is changed the stored value will not necessarily reflect the change (unless you force a recalculation, and therein lies your major problem) to the underlying value. It is therefore best simply to recalculate the result whenever it needs to be displayed.

There is plenty of discussion on this subject within these forums.

 

[alexlds]

Well MStef . . . unfortunately I DO need to do exactly that.

It is all very well saying let it recalculate using the original data - but the data will be subsequently modified by the users and they will have no record of the original

 

[MStef]

O.K. send a short example of your MDB and tell us what you want.
(Access2000 or 2002-2003).

 

[alexlds]

Thanks . . . but the project is in considerable disarray as I am working on a several issues simultaneously and sending it to you as is will probably make you tear your hair out

Might be easier to do it this way . .

Make a table with field 1 and field 2 both numeric.
The table gets its data from a query. The query consists of field 1 and eg a calculated field 3:[field 1]*0.527

What I need to do is when a new record is entered into the query by a form, is for a passive or static copy of the result in field 3 to be entered into the table field 2 along with the normal editable field 1.

If it helps, there will only ever be one record in the query

Surely this sort of thing must be fairly common ?? - Im thinking of calcs based on price at saletime - although the product price may in future be altered, a record still needs to be kept of calculated results eg the profit at the time of the sale.

 

[alexlds]

Hi John Big Booty - Surely this sort of thing must be fairly common ?? - Im thinking of calcs based on price at saletime - although the product price may in future be altered, a record still needs to be kept of calculated results eg the profit at the time of the sale. How can that be done?