Update table from Groupby query

[JShrimps, Jr.]

One query groups all received packages by date sent,
giving me a count of what was received by date sent.

I have another table with the dates packages were sent,
with a column "ReceivedCount", integer. I would like to
dynamically update the number of packages received by date sent
from the query above.

When I pull the group by query into the grid, and link the table
with the dates packages sent by the date field, click the run query icon,
I get the "Operation must use an updateable query" error.
Is there any way to update the contents of a table from a group
by query all in one step?

 

[Michel Walsh]

Hi,


No, since if you were to refresh it, your update would be flushed, the
basic table resulting back to the previous SUM, COUNT, ... You have to
change the basic tables to be safe, not the "summary". Changing the summary
does not change the content! and the data is stored in the tables, not in
the summary you have got from them.


As example, to change a COUNT, you can add or remove tables in the basic
table, to change a SUM, you have to change the values in some records (or
add/delete new records with a not-zero value, that would modify, indirectly,
the SUM ). "Updating" a MEAN, MIN, or MAX is even more complex.



Hoping it may help,
Vanderghast, Access MVP

 

[JShrimps, Jr.]

Would I am trying to say, is it possible to use the
sum/count/min/max values from
an aggregated query to
update the contents of another table in a query?

I've seen a similar question posted several times
in this news group - it is a common predicament -
I just don't remember the answer.

 

[Michel Walsh]

Hi,


In MS SQL Server, yes, with Jet, nope, but there are work around. One it
to use the Dxxx functions rather than the aggregate; another one is to make
a temp table out of the query using the aggregate, then, update using the
temp table rather than the total query. A third solution is to use an update
query based on the initial table through the required aggregated elementary
arithmetic... an example is preferable:


UPDATE summary INNER JOIN baseTable ON summary.group = baseTable.group
SET summary.SumOfAmount = summary.SumOfAmount + baseTable.Amount

for a SUM, or

UPDATE summary INNER JOIN baseTable ON summary.group = baseTable.group
SET summary.LastOfAmount = baseTable.Amount

for a LAST, or

UPDATE summary INNER JOIN baseTable ON summary.group = baseTable.group
SET summary.MaxOfAmount = iif(baseTable.Amount> summary.MaxOfAmount,
baseTable.Amount,
summary.MaxOfAmount)


for a MAX, etc.


That assumes that the summary fields are initialized properly before
starting the updates.



Hoping it may help,
Vanderghast, Access MVP

 

[JShrimps, Jr.]

Thanx for the help.
I've been making tmp tables from aggregate queries to update another
table for years.
I was just wondering if there was a way to do the same thing with a query
based on an aggreagate query.

I'll see if this works.