Solved

MSACCESS error: Operation must use an updateable query

Posted on 2009-04-08
2
2,718 Views
Last Modified: 2012-05-06
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));
0
Comment
Question by:Zarbs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Accepted Solution

by:
pskeens earned 500 total points
ID: 24104152
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
 

Author Closing Comment

by:Zarbs
ID: 31568313
Such an easy solution .... thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question