Making calculation from query appear in table ?

[looneytunes]

hi - i have tried search for the solution to my query - but to no avail, so here goes:

I have a query which is based on more than one table. In the query, I have specified a calculation, eg. Final Price: SUM([Sale Price] - [Discount])
From this query I have a form, just showing everything. Details are put into the form, and viola, they appear in the query if checked. However, they do not appear in the table
My assumption why this is not happening was because the Final Price is no longer "record source"d from the original table. How can I combat this so that it does appear in the table?
Thank you (sorry if it is easy - but i dont have a clue!)

 

[Mile-O]

The purpose of using calculations in queries and forms is because this is where to use them. You do not store the result of a calculation in a table.

 

[looneytunes]

thanks for the quick reply. so would it be safe to delete that field from the table, e.g. in my case it would be Final Price
?
thank you

 

[Mile-O]

Yes, it would be safe.

If you stored the price and then changed a detail you've have to recalculate the price and add it to the field again. By using calculations you ensure that your data/summation is correct every time.

 

[Christopherusly]

the only issue i found when not storing the result of a calculation from a query came in when persons recieved promotions and payrise, the query would then reflect the new hourly rate and not the old charged at the time to the project which would be incorrect.

 

[The_Doc_Man]

When this happens, you have discovered a non-normalized table issue. You can sometimes store such computed values to prevent this problem from occurring, but it is like putting a band-aid on a blow-out patch on a piece of duct tape. It is a workaround, not a fix. CERTAINLY not a long-term fix.

There is a normalization rule that says you store things in a table only when they depend entirely and only on the prime key of that table. If the data in your table can change over time such that you need to update the table via some computation, there is a chance that your problem is that your data depends on dates and that such dates are NOT part of the table's prime key.

The technically correct way to do this is to blend in dates so that changes are date-tagged - which usually means you must put the variable parts in another table that JOINs to the main table, then select from the JOIN query by date and/or some other criteria.

Now, I'll be a bit apostatic - you could really store your computed data in the table if you didn't want to make the excruciatingly correct solution come into being. And for a simple-minded DB, that might be good enough. There is, after all, a law of diminishing returns. But if you do take this approach, you should always keep in mind that you took a short-cut for reasons of economics, not purity of solution.

How would you redo the computation?

In the form's class module, build a VBA subroutine that computes what you want. Call this subroutine from the _AfterUpdate event routine of each control on the form that potentially could change the result of the computation. Store the result in the control corresponding to the field in your table. In other words, re-bind the control but move the computation to any contributing control's _AfterUpdate event. Then you will store the result in the record.

BUT... you didn't get that solution from me and if you claim you did, I'll deny every word of it.