How Can I Update a Table from a Totals Query?

[Mike Lempel]

I have a Totals Query that calculates values in a column that I would like
to use to update a field in an existing table. I create a new Update query
containing a join between the Table and the Totals Query, and specify the
field from the Totals query as the the update value for the Table field.
This method works fine if the Query is a Select Query. But with the Totals
Query I get the message: "Operation must use an updatable query." The
Update query won't run even though I'm not attempting to update anything in
the Totals query.

The way I've gotten around this in the past is to first store the results of
the Totals Query in a temporary table, and then run an Update Query which
contains a join between the two tables. All this gets done in a VBA module.

Is there another (hopefully better) way to do this?

Thanks very much for any help or advice.

Mike

 

[John Spencer (MVP)]

If you are updating just a few records, you can use the aggregate functions such
as DSum to get the value. This is often simpler and with a few records will
probably APPEAR to the human as being as fast. If you are doing a large number
of updates, then the way you are doing this is probably faster.

UPDATE MyMainTable
Set MySummaryField = DSum("DollarsSpent","MyOtherTable","ForeignKey=" & MyMainTable.PrimaryKey

The question I do have is WHY are you storing summary data in a table. You
normally calculate these values when needed. This would ensure that your values
are accurate as of the time you run the query to get them.

But, you know your data and the reasons for what you are doing.

 

[Mike Lempel]

John,

Thanks for your quick response. The reason I'm doing this is that there's
lots of data to summarize (about 100,000 records) into about 500 summary
records. The underlying data is static for one day, so there's no problem
scheduling this process to run before the start of business each day. There
are also additional calculations needed on the summary data, including
graphical displays, and users need to be able to view this stuff in a
real-time environment. I've tried it both ways, and calculating the
aggregate values on the fly (using domain aggregate functions) is
unacceptably slow, at least in the computing environment we're in.

What puzzles me most about the inability to use a Totals query as a record
source in an Update query is that there is nothing logically improper here.
I'm not attempting to 'update' any part of the Total query's results. I was
hoping there was a simple, clever way to get around this restriction. I'm
using Access 97. Do you know if the more recent versions of Access behave
the same in this regard?

Thanks again,
Mike

 

[John Spencer (MVP)]

As far as I know you can't use an aggregate query in an UPDATE if you are using
Access and Jet. That is the way the designers implemented it and that is the
way it is. Perhaps it was easiest to just set it up this way, perhaps the
designers couldn't find a reliable way of determining when only one value was
returned and when more than one value was returned. Or perhaps there are other reasons.

You've obviously found the best solution using a temporary table.