Update query

[peljo via AccessMonster.com]

I have an updatable query based on 2 queries.qryinput and qry output. I want
to update a field in the table products with this result. Since the query is
non updatable ,how should i proceed, can i use the dlookup function? How
should it look like? My query is the following
SELECT qryInput.ProductID, qryInput.Sum1, qryOutput.Sum2, [Sum1]-[Sum2] AS
Rest
FROM (qryInput INNER JOIN qryOutput ON qryInput.ProductID = qryOutput.
ProductID) INNER JOIN products ON qryInput.ProductID = products.Productid;

I want to update the field products.items4( table products,field items4)
with the field Rest from the query.
Can you help me ?

 

[Jeanette Cunningham]

Hi again peljo,
save that query you posted as qryA
create a new query in design view
put both qryA and your table in the query
create the appropriate join from qryA to your table
drag the field that is to receive the Rest calculation in qryA to the query
grid
on the Query menu, choose Update query
for the field in your table that rest is going into
in the row that says Update To: type [qryA].[Rest]
run the query

Jeanette Cunningham

 

[John W. Vinson]

I want to update the field products.items4( table products,field items4)
with the field Rest from the query.

Well... I would suggest that you don't.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]