Solved

MSACCESS error: Operation must use an updateable query

Posted on 2009-04-08
2
2,713 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
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now