Update Table using query (1 Viewer)

[callumwatson]

I have a TOTAL field in a table which holds a number which is the total of values in another table. I have successfully written a query to obtain the value from my other data ,however, I don't know how to get the result to automatically my TOTAL field.

I don't even know if an auto generated table field is allowed and possible.

Here is my query (probably hard to follow): -

SELECT Enquiry.[Enquiry Number] AS [Enquiry_Enquiry Number], Sum(Valuation.Value) AS SumOfValue
FROM Enquiry INNER JOIN Valuation ON Enquiry.[Enquiry Number] = Valuation.[Enquiry Number]
GROUP BY Enquiry.[Enquiry Number], Valuation.[Enquiry Number];

NOTE the TOTAL value field I want to update is in the Enquiry Table itself!!

 

[Rich]

Storing of calculated fields in any event is not recommended, to do what you are trying is definitely not recommended, use a query or a calculated control to return the sum.

 

[callumwatson]

Why do you not recommend calculated fields?

By typing a query into my text box I dont create the value in my table that I would like (which I was then going to used in a report).

 

[Rich]

In simple terms you cannot ensure the integrity of your data when storing the results of a calculation automaticaly in a table, you can add a button which will store the result, but since you already have the result you require in your query then just base the report on the query. Or base the report on the original table and add a calculated control there.
HTH