Summing multiple columns

[SDickson]

Hi new to Access but would like to be able to sum fields from multiple columns in same table to make single value in field of another column




from a table i created with 17 questions, allowing multiple numerical values per question, (ie answer to question 1 can be either 3, 6, 8 in value, the next question will have different multiple values to choose from 1,4, 7), the answers then used to apply to another 10 tables of various health measurement say Physical, mental, etc which a score is calculated ranging from 0-100 summing the various questions . Table Physical would use Question 1,8,9 mental would use Question 2,3,6,10,11,14 and so forth for the other Health measurement tables. Each of the health measurement data applies to individual clients final table will list clients and each of the calculated values for the 10 health measurements from there i would query populations of clients that show various high risk levels and so forth to see trends with the original 17 questions

Tables using:
Clients
Questionaire (17 Questions )
10 different Health profiles (Physical, mental, social, etc)
Health Profile (is sum of various health profiles multiplied by a constant value to achieve final score

[JamesDeckert]

Are you trying to save the value in the table, or see the value?
Usually you don't want to save the value in a table, but seeing the total in a query is easy
Create a query and in a blank column so TotalCol: [field1]+[field2]+[field3]

[ranman256]

Sum all the columns you want, by clicking the Summation button in qry design.
bring down the fields needed, and set to SUM.

you can add you own 'constant' in an empty field put your constant or field not getting summed.

[SDickson]

Hi new to Access but would like to be able to sum fields from multiple columns in same table to make single value in field of another column

from a table i created with 17 questions, allowing multiple numerical values per question, (ie answer to question 1 can be either 3, 6, 8 in value, the next question will have different multiple values to choose from 1,4, 7), the answers then used to apply to another 10 tables of various health measurement say Physical, mental, etc which a score is calculated ranging from 0-100 summing the various questions . Table Physical would use Question 1,8,9 mental would use Question 2,3,6,10,11,14 and so forth for the other Health measurement tables. Each of the health measurement data applies to individual clients final table will list clients and each of the calculated values for the 10 health measurements from there i would query populations of clients that show various high risk levels and so forth to see trends with the original 17 questions

Tables using:
Clients
Questionaire (17 Questions )
10 different Health profiles (Physical, mental, social, etc)
Health Profile (is sum of various health profiles multiplied by a constant value to achieve final score

[JamesDeckert]

I assumed you were adding column within each record. Ranman is thinking you are totaling a column of values across all records. So use the method which works for your situation.

[June7]

A field for each question is not a normalized data structure and will likely cause you lots of headaches, one of which is this sum you want to do.

Be aware that arithmetic with Null returns Null.

[SDickson]

Col1 Col2 Col3 Col4 Col5 Col6 C0l7
Physical = Col1 + Col4 + C0l7 times 10 = Value?
ClientA Cloumn Scores 2 1 0 0 2 1 1
Mental = Col2 + Col5 times 20=Value?


This looks much what im trying to get at the value is a score 0 to 100 for each client, and for each health profile for each client

I have Tables
Clients
Questionaire(my 17 questions with score)
do i set up 10 more tables with each profile(Physical, Mental) and how do i add different columns to get value for individual profile for each client

[June7]

Questionnaire type database has been topic of numerous threads. Did you search?