Sum 2 fields and store the value in a third field (1 Viewer)

[raghavchadha]

Lets say I have 2 fields in a table, Score1 and Score2. I would like to calculate the sum of the scores and save it in the 3rd field called TOTAL.

I'm using MS Access 2007 and was wondering how to do this without SQL or VBA.

I was able to create a query that does this successfully, but i'm not able to display that total from the query on my form. I would also like this value to be displayed as a field in another table.

Thanks, your help will be much appreciated.

 

[Rabbie]

Welome to the forum

You should never store calculated values in a table. Perhaps you should base your form on a query which includes the calculated value

 

[raghavchadha]

Thanks for the prompt response Rabbie.

I did try that as well, I created a query in which there are 2 columns, Field1 and Field2 which import the values from the table/form. In the third field, i entered: Total=[Field1]+[Field2].

On running this query, the Total is displayed correctly, but I'm not sure how to access this stored value. Am i correct in saying that a query only displays information, not store it?

My end goal is to display this TOTAL as a field in another table all together.

 

[MSAccessRookie]

Thanks for the prompt response Rabbie.

I did try that as well, I created a query in which there are 2 columns, Field1 and Field2 which import the values from the table/form. In the third field, i entered: Total=[Field1]+[Field2].

On running this query, the Total is displayed correctly, but I'm not sure how to access this stored value. Am i correct in saying that a query only displays information, not store it?

My end goal is to display this TOTAL as a field in another table all together.

It is not normal practice to store calculated data, since the Field contents are potentially subject to change each and every time the form is opened and the Field data is recalculated.

 

[akt01]

If I understand your question ...

If your form is based on a query which contains Score1, Score2 and TOTAL, then create a textbox on the form, and use TOTAL as your Control Source. It should appear as an option in the drop down box.

If you want this data to appear in Form B, then create a text box, click the three dots ... next to Control Source, then click Forms, and Loaded Forms, and navigate to Form A, TOTAL. You can have as your Control Source any data on any other open form.

good luck

 

[raghavchadha]

I see, thanks for the inputs. I'm going to try this with the approach you'll suggested.

 

[boblarson]

And just as an FYI for you -

You basically can use a query anywhere you can use a table (which a lot of people do not know so they spend time trying to make a table for a report, or an export, etc. when they do not have to do so).

 

[Atomic Shrimp]

And just as an FYI for you -

You basically can use a query anywhere you can use a table (which a lot of people do not know so they spend time trying to make a table for a report, or an export, etc. when they do not have to do so).

I agree - it's not immediately obvious you can do this - when you use the wizards, they typically present you with a choice of tables and it's easy to overlook the option to choose from the queries too.