Update query

[Db396]

Hi

I have written a couple of Select Queries that work well. However, when I turn them into Update Queries they don't work.

From reading the Help files, I think that this is because I am creating a variable in the Update Queries and also using Totalling. Both of these are necessary to obtain the required result.

I have been able to progress by converting the Update Queries into Make Table Queries. Essentially I am making a temporary table from which I then run an Update Query.

This all works and I get to where I need to.

Only question is, am I completely missing something or are Update Queries very limited in what they can do? The approach I've tried does work but just doesn't seem at all elegant.

Would be very interested to hear comments.

Thanks
David

 

[CJ_London]

Update queries don't work if you are trying to update a field using grouping and/or subqueries to calculate what the update value is.

The reason is Access needs a 'real' field value to update and grouping and subqueries produce a 'calculated' value. It can often be got round by using a function (e.g. dlookup, dmax, dfirst or one of your own making) or, as you have found, create a temporary table and then update from there.

Note: I'm pretty sure you can still use subqueries in an update query as part of a criteria however, so long as it does not form part of the update itself.