Summing specific fields from multiple columns

[SDickson2]

I have a table created that has numeric values related to a questionaire that has multiple choice answers that will give a score. Each questionaire is from a different client. I am trying to get values for 10 different Health Profiles (example: Physical,Mental,Social) Physical will only take scores from column 1,3,4 and Mental would take scores from columns 3,5,8,10 and so forth. so each client will have 10 unique health profile scores based on the original 17 questions. Each question is a separate column and each client will have a differentID. i need some help please

 

[sxschech]

Couple of ways. Assuming that column names are consistent you could write a query to add them. Since I don't know the names of your columns, I'll use the numbers and these should be replaced with the actual field names from the table because you should not use numbers for a field name.

SELECT questionID, Sum(Column1+Column3+Column4) As Physicalc, Sum(Column3+Column5+Column8+Column10) As Mentacalc, ...
FROM yourtable
GROUP BY questionID

Is the col 3 double counted or was that a typo?

 

[SDickson2]

confused where i put code? from Query table i have created, the properties is:

SELECT HealthProfile.ClientID, HealthProfile.Walkingupaflightofstairs, HealthProfile.Runingthelengthofafootballfield, HealthProfile.Sleeping, HealthProfile.[Hurting or aching in any part of your body], HealthProfile.Gettingtiredeasily, THEDUKE.Physical
FROM HealthProfile, THEDUKE;

For the Physical Profile (1 of 10 Profiles)
query table gives me client_ID(desending)in the rows and the listed column headings above, which are to be includes in total value, and the fields under each of the columns i have a value of ( 0,1,2 ) for each client, based on 5 of 17 Questions pertinent to Physical
so do I and where do i get column for the Total for each client

and yes answers to questions(Columns) can be applied to different Profies (physical,mental,social,etc)

 

[sxschech]

Does this mean that each profile is a separate table? Also, I noticed that looks like you are joining to another table called THEDUKE. Is that correct? Seems that you have two tables but no actual join?

 

[SDickson2]

Does this mean that each profile is a separate table? Also, I noticed that looks like you are joining to another table called THEDUKE. Is that correct? Seems that you have two tables but no actual join?

I was thinking is best to make each profile a separate table then each table (10 of them) would be carried over to a Table(or Query) called the DUKE where another calculation is needed on an individual ClientID's subtotal per profile in effect, the Table called the DUKE would show each ClientID Total scores for all 10 health Profiles

 

[SDickson2]

I was thinking is best to make each profile a separate table then each table (10 of them) would be carried over to a Table(or Query) called the DUKE where another calculation is needed on an individual ClientID's subtotal per profile in effect, the Table called the DUKE would show each ClientID Total scores for all 10 health Profiles

also when in Query my statement
([HealthProfile]![Walkingupaflightofstairs]+[HealthProfile]![Runingthelengthofafootballfield]+[HealthProfile]![Sleeping]+[HealthProfile]![Hurting or aching in any part of your body]+[HealthProfile]![Gettingtiredeasily]) AS TotalPhysical
FROM HealthProfile;

Is looking like 20202 instead of 6 for total

 

[sxschech]

You shouldn't need 10 tables to do this if your data a structured properly, one should do it. As for the totals, are you trying to count or sum as that would produce different results.

 

[SDickson2]

I am trying to get results Col1 (2) Col2 (0) col3 (2) col4 (0) col5 (2) col6 (0) to equal total score of 6, and not look like 202020 your help is very much appreciated

 

[sxschech]

perhaps you could provide a sample of data and expected result?

 

[SDickson2]

End Result for Physical Profile
Client(AutoNumbering) Physical = 60(total) subtotal is multiplied by Constant = total
Constant = 10
subtotal = 6
subtotal = (question 8) + (question 9) + (question 10) + (question 11) + (question 12)
(question 8) = 2
(question 9) = 0
(question 10) = 2
(question 11) = 0
(question 12) = 2
subtotal = (2 + 0 + 2 + 0 +2)

End Result for Depression Profile
Client(AutoNumbering) Depression = 42.858(total)
Constant = 7.143
Subtotal = 6
subtotal = (question 4) + (question 5) + (question 10) +(question 12) +(question 13)
(question 4) = 2
(question 5) = 0
(question 10) = 2
(question 12) = 0
(question 13) = 2

total of 17 questions that each can be applied to 10 distinct Profiles
each question is a separate column
looking for 10 different scores(profiles) for each client

 

[MarlaC]

I am trying to get results Col1 (2) Col2 (0) col3 (2) col4 (0) col5 (2) col6 (0) to equal total score of 6, and not look like 202020 your help is very much appreciated

The reason you're getting this result is that Access thinks the value 2 is a string rather than a number. As an operator, + will add numbers or concatenate strings. What is the data type you have set for each of these fields you are adding? If it's text, is there a reason for that? You can work with numbers stored as text, but in order to manipulate them as numbers (adding, multiplying, etc.) you have to explicitly convert them to an appropriate number type first, using CInt() or CLng() or CDbl(), etc.