Update Query "Operation must use an updateable query"

I have 2 tables. In one tblOption I have 2 fields OptionID(PrimaryKey) and BasePrice. In another tblOptionsPricing I have 4 fields OptionID, PartNo, Qty, LastCalculatedPrice. The OptionID & PartNo together are the primary key. I have a query qryOptionPricingSummary with the following SQL:
SELECT DISTINCTROW tblOptionsPricing.OptionID, Sum([LastCalculatedPrice]*[Qty]) AS Cost
FROM tblOptionsPricing
GROUP BY tblOptionsPricing.OptionID;
This calculates the total cost of each option. I then want to write the answer back to tblOption. I am trying to do this with an update query as follows:
UPDATE DISTINCTROW tblOptions INNER JOIN qryOptionPricingSummary ON tblOptions.OptionID = qryOptionPricingSummary.OptionID SET tblOptions.BasePrice = [PriceTotal];
When I run the query I receive an alert saying "Operation must use an updateable query".
Why is this and how can I fix it?
awestropeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
peter57rConnect With a Mentor Commented:
awestrope,
Try this instead: it avoids the aggregate query.

 UPDATE  tblOptions inner join tblOptionsPricing
on tbloptions.optionid = tbloptionspricing.optionid
 SET BasePrice = nz(BasePrice) + ([LastCalculatedPrice]*[Qty])


Pete
0
 
tbsgadiCommented:
Hi awestrope,
You cannot update an aggregate query.(one with groupby,sums etc)
You can use a dlookup/dsum function instead

Good Luck!

Gary
0
 
peter57rCommented:
Hi awestrope,
Remove the Distinctrow to start with.  It is meaningless in a Group by query.
What is PriceTotal?

Pete
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
awestropeAuthor Commented:
tbsgadi: are you saying you can't use an aggrgate query to update information in a table? What is the logical reason for this?
peter57r: PriceTotal should read Cost.
0
 
awestropeAuthor Commented:
But I need to do the aggregation as there are multiple rows in tbloptionspricing
0
 
peter57rCommented:
That's OK.  The query does multiple updates per OptionID.

Pete
0
 
awestropeAuthor Commented:
Cunning - like it!
0
All Courses

From novice to tech pro — start learning today.