sum up values in a table

[ariel81]

how do i sum up all the values in a table with reference to the column mth/year e.g: yr 2007 and put the result in another table?

refer the jpeg image.

 

[Rich]

You shouldn't store a calculated value, use a Query to get the result

 

[ariel81]

how do i use a qurey?

 

[ariel81]

i use the code to sum up values and store into another table:

Set Db = CurrentDb()

sql = "Insert Into tblQPIYearly (Select InputYear, Sum([Monthly TotalPF]), Sum([Monthly Rejection]), Sum([Monthly TotalAvoid]) From tblQPIMonthly Group By InputYear)"

Set rs = Db.OpenRecordset(sql)

db.close

however, there is syntax error in Insert Into statement. anyone here can help me?

 

[The_Doc_Man]

While you surely can store data via VBA and Aggregate functions, there is hardly ever (I won't say NEVER) justification for storing it, since it changes if you add or remove or update just one record. And because it is statically stored, it won't update automagically. If you make a query that is a summation query containing the items you want summed, any time you open the query, you have an up-to-date sum.

Look up Summation queries if you are not sure about them.

 

[Moniker]

I agree that you should almost never store a calculated value for the reasons others are giving you. And, the syntax error in your SQL is that it's missing the semi-colon - ; - at the end.