How can I add these cells together?

[Wookiefoot]

I have a table that is made from a query. I'm trying to add certain cells from the same row into the last column. In the image below I need to add each cell under a header that contains Standard Price in the header.


In the sample image below, the first row under the headers, under the total cost column would equal .12022. The 2nd row would equal .45739.

Is there something I could add to my SQL below or do I create an update query to populate this column. Thanks for the help!


SQL that makes the table currently

UPDATE MasterFurcation SET MasterFurcation.[HS 1 Stock1100] = 0, MasterFurcation.[HS 1 Standard Price] = 0, MasterFurcation.[HS 2 Stock1100] = 0, MasterFurcation.[HS 2 Standard Price] = 0, MasterFurcation.[Mesh Netting Stock1100] = 0, MasterFurcation.[Mesh Netting Standard Price] = 0, MasterFurcation.[AL Tube 1 Stock1100] = 0, MasterFurcation.[AL Tube 1 Standard Price] = 0, MasterFurcation.[AL Tube 2 Stock1100] = 0, MasterFurcation.[AL Tube 2 Standard Price] = 0, MasterFurcation.[Pulling Eye Stock1100] = 0, MasterFurcation.[Pulling Eye Standard Price] = 0, MasterFurcation.[FT Blue Stock1100] = 0, MasterFurcation.[FT Blue Standard Price] = 0, MasterFurcation.[FT Orange Stock1100] = 0, MasterFurcation.[FT Orange Standard Price] = 0, MasterFurcation.[FT Green Stock1100] = 0, MasterFurcation.[FT Green Standard Price] = 0, MasterFurcation.[FT Brown Stock1100] = 0, MasterFurcation.[FT Brown Standard Price] = 0, MasterFurcation.[FT Slate Stock1100] = 0, MasterFurcation.[FT Slate Standard Price] = 0, MasterFurcation.[FT White Stock1100] = 0, MasterFurcation.[FT White Standard Price] = 0, MasterFurcation.[FT Red Stock1100] = 0, MasterFurcation.[FT Red Standard Price] = 0, MasterFurcation.[FT Black Stock1100] = 0, MasterFurcation.[FT Black Standard Price] = 0, MasterFurcation.[FT Yellow Stock1100] = 0, MasterFurcation.[FT Yellow Standard Price] = 0, MasterFurcation.[FT Violet Stock1100] = 0, MasterFurcation.[FT Violet Standard Price] = 0, MasterFurcation.[FT Rose Stock1100] = 0, MasterFurcation.[FT Rose Standard Price] = 0, MasterFurcation.[FT Aqua Stock1100] = 0, MasterFurcation.[FT Aqua Standard Price] = 0, MasterFurcation.[Total Cost] = 0;

[ArviLaanemets]

a) The column(s) with formula(s) must be included into QueryTable (a Table created by query) - probably as rightmost one(s);
b) The same formula must be in entire column;
c) In Data>Properties (in Connetions section) 'Preserve column sort/ilter/layout' must be checked.

[davegri]

You don't want to store a total in the table.
Here's why: For whatever reason if a Standard Price changes in the future, the total is not recalculated, so it's WRONG.
Any time Total Cost is required, calculate it on the spot with a query and use that total. Guaranteed to be based on latest data.

[ArviLaanemets]

Scrap it - I mistaked it for Excel Forum!

[Wookiefoot]

You don't want to store a total in the table.
Here's why: For whatever reason if a Standard Price changes in the future, the total is not recalculated, so it's WRONG.
Any time Total Cost is required, calculate it on the spot with a query and use that total. Guaranteed to be based on latest data.

Thanks. I setup a query to add the fields and everything works perfect.
However, I need to limit the totals to 3 decimal places. How would I go about that?

[HS 1 Standard Price]+[HS 2 Standard Price]+[Mesh Netting Standard Price]+[AL Tube 1 Standard Price]+[AL Tube 2 Standard Price]+[Pulling Eye Standard Price]+[FT Blue Standard Price]+[FT Orange Standard Price]+[FT Green Standard Price]+[FT Brown Standard Price]+[FT Slate Standard Price]+[FT White Standard Price]+[FT Red Standard Price]+[FT Black Standard Price]+[FT Yellow Standard Price]+[FT Violet Standard Price]+[FT Rose Standard Price]+[FT Aqua Standard Price]

[Wookiefoot]

Answered my own question...

I used the Round function in my update query to set the decimal places to 3.
ex:

Round(

[FT Yellow Standard Price]+[FT Violet Standard Price], 3)

[davegri]

In the query design view, click on the totals column, then user property sheet (F4 will toggle view) to set the format to Standard, decimal places to 3.