Update query using another query

[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

 

[John Vinson]

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.

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.

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.

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.

 

[Frank M.]

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.