Error: Operation must use an updateable query

[Max Moor]

Hi All,
Before archiving some old records, I store historical data. I run a
query that sums sales amounts for different categories. Then, I want to
use this query in an update query to store the totals in a table. I have
the table to be updated and the totals query joined in the update query.

When I run the update query, it fails, telling me the query must be
updateable. I'm not trying to update the query though. I just want to
copy values from its recordset into the table. Ican do it by hand in code
easy enough, but it seems silly that I should have to. Can anyone point me
to a workaround or trick to make this work?

- Max

 

[Ted Allen]

Hi Max,

An update query revises existing data in a table. It
sounds like you want to append the data to a table (add
new records). If this is the case, try changing your
query to an append query.

HTH, Ted Allen

 

[John Vinson]

Hi All,
Before archiving some old records, I store historical data. I run a
query that sums sales amounts for different categories. Then, I want to
use this query in an update query to store the totals in a table. I have
the table to be updated and the totals query joined in the update query.

When I run the update query, it fails, telling me the query must be
updateable. I'm not trying to update the query though. I just want to
copy values from its recordset into the table. Ican do it by hand in code
easy enough, but it seems silly that I should have to. Can anyone point me
to a workaround or trick to make this work?

No Totals query is ever updateable... even when it logically should
be. You can use a totals query as an Append if you can create new
records for your historical archive, or you can use the DSum()
function to do the totalling instead of a Totals query.

 

[Max Moor]

Hi Max,

An update query revises existing data in a table. It
sounds like you want to append the data to a table (add
new records). If this is the case, try changing your
query to an append query.

Hi Ted,
I should have been more clear. I have an append query that runs
previously to create the records. The totals part couldn't be part of that
query, so I was trying to do an update query subsequntly. Anyway, I just
wroote code and used DSum. It seems fast enough. Thatks for the response,
in any case.

- Max

 

[Max Moor]

No Totals query is ever updateable... even when it logically should
be. You can use a totals query as an Append if you can create new
records for your historical archive, or you can use the DSum()
function to do the totalling instead of a Totals query.

I ended up using DSum, just as you suggest. The records already exist in
the table, so I couldn't do an append. Oh, well. I was just afraid that
doing DSum in code would be to slow. It turned out fine though. Thanks
for the info.

- Max