Ms-access – May a query update a source table

ms access

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.

Best 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
Related Question