Operation must use an updateable query

[parry]

Hi, the above error message is driving me insane, and appears when I am trying to run an update query on a table, based on data from a query.

What I am trying to do is use data in a field on the table to obtain a value for another field. This value is based on a percentage.

For example the value of field1 (say $10) for a record as a percentage of the sum of all field1 totals (say $100) would equal 10%. The value of field2 is derived by using this percentage against a set figure (say $200), so the value of field2 should be $20.

Field2 currently has no data and I want to update the field as per above.

Firstly I created a query with an expression that obtains the percentage (but just expressed as a number - 0.20 would be 20%) for each record. This percentage expression field is just in the query not an actual field in the table.

I then created an update query that includes the table I want updated plus this query that holds the percentage for each record in the table. I then just take the figure $200 multiplied by the percentage as the data to update but..... I get that bloody error message.

Looking at the help has given me no help as its very poorly explained. COuld someone expolain why this error is occuring and what I need to do to fix the problem.

cheers
Parry

 

[dk]

Hello mate,

I'm not entirely sure what you're doing but you can't update data on any query which uses any aggregrates in it (e.g. sum, avg, max). Can you post the SQL of your query and if that query uses any other queries post the SQL of that too?

 

[parry]

Hello mate,

I'm not entirely sure what you're doing but you can't update data on any query which uses any aggregrates in it (e.g. sum, avg, max). Can you post the SQL of your query and if that query uses any other queries post the SQL of that too?

Thanks Dan, I wasnt aware of that limitation. Seems ridiculous to me as often you may want to use summing or averages in a query then update data using this info. In the end I used a make-table query, then did an update query off the new table.