Update Query must use an updateable query (1 Viewer)

[Danick]

I have a table that has multiple duplicates. I'd like to run a "Group by" Totals query and then use an update query to update a temporary table with those results. But I get an error message, "Operation must use an updateable query".

Is there a workaround for this or another way to do what I'm trying to do?

Thanks

 

[theDBguy]

One workaround is to use DLookup().

 

[plog]

Turn your query into a MAKE TABLE. Then either use that table or you should be able to update your prefered table with it.

 

[Pat Hartman]

If you don't care what "record" of the multiples applies, then you don't need to group them. For an update, all of them will update as long as they match the join fields. So 5 records might match and they will just keep changing the value to the same thing. If you do an append and the "duplicate" fields are part of a unique index, then only the first one will be saved and the rest will be discarded. Also, the append query can in fact use aggregated data so you can group by in the append.

 

[June7]

Saving aggregate data is usually a bad idea. If it can be calculated for UPDATE it can be calculated when needed.

If it's a 'temp' table then delete all the records and do an INSERT.

So what is purpose of temp table? What process are you trying to accomplish that needs temp table?

 

[Pat Hartman]

@June7 I don't think this is a case of saving calculated values. It sounds like there are duplicate records that the OP is trying to eliminate.

 

[isladogs]

Try changing the update query to use unique records
Change the SQL from UPDATE . . . to UPDATE DISTINCTROW . . .

 

[Danick]

The Make Table query turns exactly what I'm trying to do. The only shortcoming is that it deletes the fields as well, not just the data from the fields. I have a few other fields in the temp table that I'd like to keep. Not a problem though as I can just add the empty fields to the query.

Basically, I'm just trying to automate selecting all the data in the Grouped query and them copy, append/paste those fields into an empty temp table.

Thanks for the help.

 

[ebs17]

another way to do

What do you really want to do?

update a temporary table

Temporary intermediate tables are often used when you cannot create proper queries and you therefore want to manage with individual steps. In addition, update queries in Jet when using linked subqueries are just stupid, actually correct syntax is not understood.

But I think the whole process is a workaround. Perhaps you are able to describe the initial situation as well as the actual goal. It would be conceivable to probable that there are other, more direct and simpler ways.

 

[MajP]

If you need to update a table from another non-updateable query you can always do this using a recordset to write to the table.
But I also suspect this may be a workaround, since normally you do not have a need to save aggregate data.

 

[Danick]

What do you really want to do?

Temporary intermediate tables are often used when you cannot create proper queries and you therefore want to manage with individual steps. In addition, update queries in Jet when using linked subqueries are just stupid, actually correct syntax is not understood.

But I think the whole process is a workaround. Perhaps you are able to describe the initial situation as well as the actual goal. It would be conceivable to probable that there are other, more direct and simpler ways.

This is another case where the original data to be analyzed comes from a system driven Excel Spreadsheet. The data has all the relevant data, but it's filled with duplicate data. The output needs to be to another spreadsheet that uses pivot tables, charts, etc. It can all be done in Excel, but I prefer doing it in Access where I can design nice forms and automation buttons to help with the analysis. So I take this system table, run a group query, import to create a temp Access table where the analysis is done and then export back to an Excel Spreadsheet that has all those charts already build in.

 

[ebs17]

All right: You can't design direct solutions to the concepts of grouping and analyzing.

nice forms and automation buttons

... are also a nice target.

 

[isladogs]

I would avoid using a make table query if possible as it will cause database bloat.
Although that will work it should only be used if no other better method exists.

Did you try the solution I suggested in post #7?
Doing that not only prevents duplicate records being transferred but it often makes a read only query updateable

 

[June7]

I use 'temp' table as well but table is permanent and data is temporary. Use SQL DELETE and INSERT. This is also supposed to cause db bloat but has never been an issue for me. In fact, I have read recommendations to use MAKE TABLE to avoid bloat?!?!?

 

[isladogs]

I use 'temp' table as well but table is permanent and data is temporary. Use SQL DELETE and INSERT. This is also supposed to cause db bloat but has never been an issue for me. In fact, I have read recommendations to use MAKE TABLE to avoid bloat?!?!?

Both INSERT and MAKE TABLE cause bloat. If you are using a TEMP table or a MAKE TABLE query then transferring the data to a permanent Access table, you are adding the data twice. Sometimes unavoidable but there is often a better way

 

[June7]

Raw data is already in permanent tables. I use temp tables for complex data manipulation and reporting processes.

 

[Danick]

Did you try the solution I suggested in post #7?
Doing that not only prevents duplicate records being transferred but it often makes a read only query updateable

Yes I tried it. Just changed the SQL from UPDATE to UPDATE DISTINCTROW. But same thing happened. As for bloat, yes this is true. But a quick database compact fixes that. Plus the original data is from linked excel tables. I'll try to see why changing the SQL isn't working. But for now, at least I have a working workaround that gets the job done. Thanks again for your help.