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
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?