Update Query

[Gismo]

Hi all,

I have a query to calculate my total sum stock on hand
I then have a second query where I sum all the product together to get a 1 line per product

I then link this query in an update query

Seems like Access does not like to have a linked sum query in an update query

Any way around this?

 

[arnelgp]

you cannot use Total query in an Update query.
you need to save the Total query to a temp table
and use the temp table to update your table.
or you can Dlookup the value from the Total query and
update your table.

 

[Minty]

I'm not sure you should be storing the result of such calculations, but assuming you have a sensible reason can you post up the SQL of the first two queries and the update query?

 

[theDBguy]

I agree with using DLookup().

 

[Pat Hartman]

Using domain functions such as dLookup() in queries (or code loops) is OK as long as you don't have a lot of records. Just be aware that the query can get slower and slower as the recordset size grows. The domain function must run a query itself. So, if you have 1000 records, you have to run 1000 extra queries so domain functions are extremely inefficient in this context.

Also, it is probably wrong to store the calculated value anyway so you shouldn't be doing it.