How do I sum many records in an update query using Access 2.0?

[Sirkenny]

I need to sum the amounts in the formula record, subtract the total from 100, and update the last record with the difference.
Here is how the "Select" query looks.

FormCode/FormCode.ProdCode/% to use/MastForm.ProdCode/FoCoID
407 ID-900 0.19 IDT-11 4367
407 ID-40 0.3843 IDT-11 4368
407 ID-300 0.16 IDT-11 4369
407 ID-970 0.02 IDT-11 4370
407 ID-1200 0 IDT-11 4371
(counter)
I need to update "ID-1200" with difference of 100 minus the sum of the other ingredients. When done ID-1200 should equal .2457
Here is the SQL statement from my "Select" query.

SELECT DISTINCTROW FormulaComposition.FormulaCode, FormulaComposition.ProductCode, FormulaComposition.PercentageToUse, MasterFormulas.ProductCode, FormulaComposition.FormulaComponentID
FROM MasterFormulas INNER JOIN FormulaComposition ON MasterFormulas.FormulaCode = FormulaComposition.FormulaCode
WHERE ((FormulaComposition.FormulaCode=407));

Now I would like to just change my "Select" query to an "Update" query and do the calculation for "ID-1200" in the "Update to" field.

I am new to Access and using an old Access 2.0 program, there is very little out on the web for 2.0 "How to's".
I have over 685 formuls that need to be updated in this way and the igredients vary from 2-10 items.

Any help would be greatly appreciated.
Thank you for your time in this matter.

 

[JerryDennison]

You should not store calculated results in your database tables. These values should be re-calculated on demand.

 

[Sirkenny]

JerryDennison:

Thank you for you reply. I agree with your comment.

But this is an "Update" once Query. I need to have the formula equal 100. In this case it only equals 75.43 so I need to sum the formula and update the last item so the formula will equal 100 again. After I update the database the query will never be run again. This is a run once query.

I quess my real question is "What is the correct syntax to get the query to update my record?"

 

[JerryDennison]

My point still stands, you should not store this value. When you need to determine the difference of the sum of any Form group from 1 this can be calculated on demand. Using a Totals query you can group on the FormCode, Sum the % field, then use 1-SumOf% to determine the ProdCode ID-1200 value.

 

[Sirkenny]

JerryDennison:

Thank you for your reply.

I already know what the "ID-1200" value should be, I need to update the database with the new values. This is why I need a "Update Query" to perform the task.
So again I ask "Would you show me how to do this in an Update Query?"

 

[JerryDennison]

Actually, I just did. Take the totals query to determine the value you need to add to the table. Then convert this into an update query. You'll need an unbound field to define the ProdCode value while using the calculated control as the source for the summary field. To use any field you simply enclose it with []'s.

 

[JerryDennison]

OOPS, not an update but an append query. Sorry bout that.