update query: operation must use updateable query

[Cowboy_BeBa]

hey all

this one from what i understand is a common problem and one ive run into before

Ive got several queries that crunch a bunch of numbers, (qryFGCost1 to qryFGCost7), once done i need to update the cost field of one of my tables with the resulting cost in qryFGCost7, when i try to run the query i get the old "Operation must use an updateable query" error

Now ive encountered this before and the solution was to do a make table query (using the other query, in this cast FGCost7), then update the other table from the freshly made table, then delete the datble

This is very doable, but so far i have about 8 or 9 operations in my DB that do this and it feels quite sloppy to me, im in the process of redesigning this DB from scratch (as there are a whole lot of new features i never accounted for and ive come up with a much better design) and id like to avoid having to use this solution in the new version

is there another way to get around this error, without resorting to creating temporary tables?

thanks,
ben

 

[MarkK]

There's not enough info in your post to speculate on how to do so, but in answer to your question . . .

is there another way to get around this error, without resorting to creating temporary tables?

. . . yes, almost certainly.

To discuss how, post your query text, and maybe the tables structures, and maybe the relationships between them.

 

[Cowboy_BeBa]

sure mark, theres alot to it, so ill just give you the basics, lemme know if you need any more detail

for starters ive attached a quick ERD of the db, just note there are many more tables than are shown here and there are many more fields in the tables shown, ive just focused on the fields and tables that are used in these queries

basically qryFGCost1 to qryFGCost 6, are designed to work out the total cost per unit of a product, below is a quick summary of each one, its a lil convoluted so please accept my appologies, ill try to be brief

fgCost1- uses tbl recipes and tblingredients sorts by prod id, ingid, recpercentage and ingprice (in that order), then creates two new fields, totperkg(which is just recpercentage divided by 100) and listcost which is totperkg*ingprice

fgcost2- uses the first query and groups by product id, sums recpercentage (which should total to 100%, if it doesnt i know theres an ingredient missing or a problem in the formula), sums totperkg and listcost

fgcost3- uses tblFinishedGoods, tblFGComponenets and qryFGCost2
sorts by FGID and prodID (a finished good may have multiple products in it), creates a new field (componentCost) which multiplies fgcQTY, by fgcPchSize, by SumofListCost

fgcost4, just groups by fgID and sumps the componentCost (so each finished good now has the total cost for all products/components used in it)
sumofComponentCost

so, to summarize the component cost its essentially tblFGComponents.fgcQTY*tblFGComponents.fgcPchSize*Sum(tblRecipes.recPerc*tblIngredients.ingCost)
summed up with the component costs for all components in each finished good

next i need to work out the bill of materials (basically just packaging and occaisionally an ingredient, ie, some finished goods may have a 50grams of choc chips or some baking powder in them, instead of creating a new product we just add that ingredient to the bill of materials, packaging is also stored in tblIngredients (in the new db this tale would be tblRawMaterials, as that makes more sense)

so fgcost5 uses qryfgcost4, tblBillOfMaterials and tblIngredients
sorts by fgID and sumofComponentCost, creates new field BillCost which is just tblBillofMaterials.bomQTY*tblIngredients.ingPrice

fgcost6- groups by fgID and sums all ingredients in the bill of materials table that are linked to that finished good (again its a one to many relationship)

finally
fgcost7- based on fgcost6 just groups by fgID, sumofcomponentcost and sumofbillcost, new field called totalFGCost which is just (sumofbillcost+sumofcomponentcost)*1.1 (i multiply by 1.1 to add a 10% labour cost, just to roughly estimate the work involved in making each unity)

this finally gives me a cost price for each unit
as ingredient prices change almost weekly i need to update this every time i print a valuation report, which is why im creating/implementing the update query, to keep the values up to date

ive attached a text file that has all the sql for each query, theres alot more fields in each query than the ones covered in the explanation above, i just figured for the purpose of the explanation id just give you the key fields that relate to what im trying to do (the rest is just additional info that is used on the report or to help me read several of the queries in case i wanna look at more detailed info)

thanks for taking the time to read all this

 

[MarkK]

this finally gives me a cost price for each unit
as ingredient prices change almost weekly i need to update this every time i print a valuation report, which is why im creating/implementing the update query, to keep the values up to date

But here's what I don't get: If ...
1) the costs change all the time, and
2) you have a query that calculates those costs
... then why don't you just...
3) run the query in 2) when you need to know the costs.

Why bother to save data that is subject to change, when you can calculate it on-demand from a query?

Some data should not be stored, like someone's age. You store someone's birthdate, and then you write a little-black-box-function that calculate age. Why don't you, if you have a query that works to render your costs, run that query as required? Why save those results and force yourself into the make-work project of having to also keep them up-to-date?

See what I'm saying?

 

[Cowboy_BeBa]

i see what youre saying and normally id agree with you that it should not be a stored field

however in this case the rest of the db's users need to view the cost price in a few different forms and reports, the reports are easy, id just need to edit the queries a bit and link in qryFGCost7, and we're done

i guess i could do the same thing for the forms, but so far it was just easier to update the values than all the forms

definitely something ill keep in mind for the next version though

 

[GregoryWest]

Don't have the users go after the table directly have them reference a query. Then in the field have a calculation. Something like this:
Field:QTY
Field:Cost
Variable:Extended:[QTY]*[Cost] The Access SQL code would look like this:
SELECT public_parts.onhand, public_parts.lastcost, [public_parts]![onhand]*[public_parts]![lastcost] AS Extended
FROM public_parts;