I work with a few tables with given data. I run a query built in Design View just like
result:![table1].[qty]*![table2].[expenses]
and I get the output in query table in field result. Is it possible to write result back to table2.resultfield? So this way my query does not only produce output table but updates one of the source tables.
-
I suspect that this question should be on Stackoverflow– RemouOct 24, 2012 at 9:57
-
I could remove this question. Thanks for advices. Just found a solution for Q– Yurij73Oct 24, 2012 at 19:42
Add a comment
|
1 Answer
You cannot update a field in the underlying table of a form when the control is bound to a function (a calculation in this case). However, you can run an update query, use VBA to update the field, use VBA to write the calculation to a bound control, or use a calculated field (MS Access 2010 and later versions only).
However, you must ask yourself if you really want to store a calculated result. It will depend on whether qty and expenses change over time and whether you need a fixed result for the current values. If qty and expenses will always be the same, just use a query to show the result of the calculation. For example:
SELECT t1.qty * t2.expenses As Total
FROM table1 t1
INNER JOIN table2 t2
ON t1.ID = t2.MatchedID
-
In fact. You are right about need to think choice beetwen simple output query use– Yurij73Oct 24, 2012 at 19:28