easy way around "not updateable query" for sums?

  • Thread starter Thread starter Phil Smith
  • Start date Start date
P

Phil Smith

I have a table. I am trying to update that table based on a query. The
query involved used sums. When I try to this, I get "this is not an
updatable query." I know the cause for it, the fact that the query has
sums.

I also know the way around it. Run my query as a make table query, then
build my update query from that.

This project would have me doing this a couple of dozen times. I really
do not want to build 24 make table queries, 24 update queries, and then
have to delete those 24 tables. If nothing else, I would expect that
would seriously fragment my databse, with a resulting performance hit.
Experience tells me that Access will just crap out after a while.

There has gotta be a better way. Anybody know of one?
 
You mighty be able to use the DSUM function.



Of course if there is a large number of records involved this could
become quite s l o w


UPDATE TheTable
SET TheField = DSum("SomeField","SomeTable or Query", "WHERE STRING
without the where")

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Phil Smith said:
I have a table. I am trying to update that table based on a query. The query
involved used sums. When I try to this, I get "this is not an updatable
query." I know the cause for it, the fact that the query has sums.

I also know the way around it. Run my query as a make table query, then build
my update query from that.

This project would have me doing this a couple of dozen times. I really do
not want to build 24 make table queries, 24 update queries, and then have to
delete those 24 tables. If nothing else, I would expect that would seriously
fragment my databse, with a resulting performance hit. Experience tells me
that Access will just crap out after a while.

There has gotta be a better way. Anybody know of one?
Click to expand...

Yeah, don't store the result of calculations. This is a primary rule of proper
database design. That is why you are finding what you think you need to do so
difficult. Just calculate the sums when you need them using your sum query.
 
Ahh, but this IS when I need them. If I do not piece this thing out,
and try to run it under one query, that query takes about 6 hours to
run, and it is needed to be run several different times with slightly
different parameters. I can create this table with all of the basic
data pulled in, and then each final run with it's different set of
paramters only takes about ten minutes each. so 6 hours plus 60 minutes,
or 6 hours plus 36 hours....
 
Back
Top