MSACCESS error: Operation must use an updateable query

I am getting the error "Operation must use an updateable query."

I understand why I am getting the error but this is what I need to do.

1. I have an ODBC link to a table which holds the specific gravity (measure of weight to volume) of materials and manufactured products
2. Some materials are manufactured items (intermediates) so the SG is calculated based on the mix of all the materials within the manufactured product and then stored in the same table
3. A finished good can be comprised a mixture of purchased and manufactured products

When you update the SG of a purchased material or change the formula to make the product the SG's of intermediates and finished products are not automatically updated so I decided to write an "Update" query to maintain them .... but alas because I need to update the same table I need to calculate the results from I am not allowed.

How do I overcome this?


This is the 4 part query I have written that doesn't work in the update but gives the correct result in the 'select' view:

Query: 330-01 Formulakey Kilos and Litres
SELECT FMMAST.Formkey, Sum(FMITEM.wtorunitqty) AS Kilos, Sum([wtorunitqty]/[specgrav]) AS Litres
FROM (FMITEM INNER JOIN FMMAST ON FMITEM.Formkey = FMMAST.Formkey) INNER JOIN INMSPEC ON FMITEM.Itemkey = INMSPEC.Itemkey
WHERE (((FMITEM.Linetyp)="I"))
GROUP BY FMMAST.Formkey;



Query: 330-02 Formulakey Specific Gravity
SELECT [330-01 Formulakey Kilos and Litres].Formkey, [330-01 Formulakey Kilos and Litres].Kilos, [330-01 Formulakey Kilos and Litres].Litres, [kilos]/[litres] AS SpecificGravity
FROM [330-01 Formulakey Kilos and Litres];



Query: 330-03 Itemkey Specific Gravity
SELECT [330-02 Formulakey Specific Gravity].Formkey, INMAST.Itemkey, INMAST.Itemdescription1, [330-02 Formulakey Specific Gravity].SpecificGravity AS CalculatedSpecificGravity, INFSPEC.Itemstat, [specgrav]/[calculatedspecificgravity] AS SGRatio
FROM INMSPEC INNER JOIN ((INMAST INNER JOIN INFSPEC ON INMAST.Itemkey = INFSPEC.Itemkey) INNER JOIN [330-02 Formulakey Specific Gravity] ON INFSPEC.Formkey = [330-02 Formulakey Specific Gravity].Formkey) ON INMSPEC.Itemkey = INMAST.Itemkey
WHERE (((INMAST.Itemkey)="AAK0106") AND ((INMAST.Itemtype)="F"))
ORDER BY [330-02 Formulakey Specific Gravity].Formkey, INMAST.Itemkey;



Query: 330-04 Correct Specific Gravity
UPDATE INMSPEC INNER JOIN [330-03 Itemkey Specific Gravity] ON INMSPEC.Itemkey = [330-03 Itemkey Specific Gravity].Itemkey SET INMSPEC.Specgrav = [330-03 Itemkey Specific Gravity]![CalculatedSpecificGravity]
WHERE ((([330-03 Itemkey Specific Gravity].SGRatio)<>1));
Paul BurrowsAsked:
Who is Participating?
 
pskeensConnect With a Mentor Commented:
Create a TEMP Table and append your results to this table, then update the records in your MainTable with the records in the TEMP table, after this is complete, delete the TEMP Table.

0
 
Paul BurrowsAuthor Commented:
Such an easy solution .... thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.