Update query using another query

  • Thread starter Thread starter Frank M.
  • Start date Start date
F

Frank M.

I have trouble using a query as the basis in a second
update query. The update query is for example:

UPDATE tab1 RIGHT OUTER JOIN qry_sub
ON tab1.CustNo = qry_sub.CustNo
SET tab1.CustNo = qry_sub.CustNo,
tab1.Total = tab1.Total + qry_sub.Total
etc.

Now, the query qry1 is an aggregation query, i.e. some of
it's fields is aggregated values coming from a more
detailed sales table.

When I run the updating query (qry_sub), I get the error
message: The action has to have an updatable query
(translated as I don't use an English Office XP). Now,
qry_sub is probably not updatable (since it aggregates),
but why does it have to be; no values are assigned to any
of it's fields; they are only taken from it and assigned
to tab1 - so I don't see the need.

The only way around this, as I see it now, would be to
have a temporary table that I could dump qry_sub result
to, and then use that table in the update query, but this
will take extra space in the base and processing time, so
I would rather avoid it, if possible.

Any suggestions will be much appreciated.


Regards,

Frank
 
Now, the query qry1 is an aggregation query, i.e. some of
it's fields is aggregated values coming from a more
detailed sales table.
Click to expand...

That's why the entire query is not updateable. Any query containing a
Totals operation is blocked from updating.
When I run the updating query (qry_sub), I get the error
message: The action has to have an updatable query
(translated as I don't use an English Office XP). Now,
qry_sub is probably not updatable (since it aggregates),
but why does it have to be; no values are assigned to any
of it's fields; they are only taken from it and assigned
to tab1 - so I don't see the need.
Click to expand...

I don't either, and it's frustrating and annoying, but it's just the
way Microsoft developed Access: no Totals query, nor any query
containing a Totals query, is updateable even if it logically ought to
be!
The only way around this, as I see it now, would be to
have a temporary table that I could dump qry_sub result
to, and then use that table in the update query, but this
will take extra space in the base and processing time, so
I would rather avoid it, if possible.
Click to expand...

The other option is to use the DSum() function rather than a Totals
query to total the values, or (performance allowing) don't store the
totals at all - just recalculate the totals on the fly.
 
Thanks for the help.

The Dsum looks interesting - may come in handy in
programming. I do not think, however, it will do here.
The summary query is quite complicated involving even a
third table with summary groups for the detailed sales.
The summarized sales with totals pr. customer, product
group are then added (create or update) to a permanent
summary table with sales pr. month, pr. customer, pr.
product group.

I am not sure whether it could be calculated on the fly,
but just to keep the overview and not make any errors, I
think I will go with the temporary table.


Regards,

Frank M.
 
Back
Top