awestrope
asked on
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
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.Op
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?
SELECT DISTINCTROW tblOptionsPricing.OptionID
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.Op
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?
Hi awestrope,
Remove the Distinctrow to start with. It is meaningless in a Group by query.
What is PriceTotal?
Pete
Remove the Distinctrow to start with. It is meaningless in a Group by query.
What is PriceTotal?
Pete
ASKER
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.
peter57r: PriceTotal should read Cost.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
But I need to do the aggregation as there are multiple rows in tbloptionspricing
That's OK. The query does multiple updates per OptionID.
Pete
Pete
ASKER
Cunning - like it!
You cannot update an aggregate query.(one with groupby,sums etc)
You can use a dlookup/dsum function instead
Good Luck!
Gary