Create a sum query based on specific values

[moyi]

Hello all, thanks for answering my question.
I have a form with 13 fields. Each field has can be coded as "1 = yes, 2= suspected, 3=no, 999=don't know, 888 = not applicable, 998 = refused to answer".
I need to sum these 13 fields together ([SUM13]), but 999, 888, or 998 should be treated as missing or zero.
Is there a way to build a query or a VBA form based on this requirement?
Many thanks!

 

[Ranman256]

make a lookup table to convert the values
ActualVal, SubVal
1,1
2,2
99,0
888,0

in query Q1, join the data table to tLookup table bring in SubValue
sum all the columns

in query Q2, sum the row (of sums in Q1) into 1 total.

 

[bob fitz]

Is the form bound to a table. If so, does the field in the table store the number (2) or the text (suspected)
BTY you have fields in a table/query and controls on a form. Controls may or may not be bound to fields a table/query.

 

[moyi]

Thank you Ranman, but I need the sum to be automatically shown up once the users enter their answers. I can't create a table manually later.

make a lookup table to convert the values
ActualVal, SubVal
1,1
2,2
99,0
888,0

in query Q1, join the data table to tLookup table bring in SubValue
sum all the columns

in query Q2, sum the row (of sums in Q1) into 1 total.

 

[moyi]

Hey Bob,

Yes, it is bounded to a table and the field stores number instead of the text.
The sum needs to be automatically shown once the users give the answers to these 13 fields.

Many thanks.

Is the form bound to a table. If so, does the field in the table store the number (2) or the text (suspected)
BTY you have fields in a table/query and controls on a form. Controls may or may not be bound to fields a table/query.

 

[plog]

My take on this is that you have incorrectly set up your table. You keep mentioning 13 fields. My guess is that you literally have a table were one record has a column for each of those 13 values. That's wrong. With that structure you have little choice but to compile a long 13 part equation to get the sum you want:

=Iif(Field1>10, 0, Field1) + Iif(Field2>10, 0, Field2) + Iif(Field3>10, 0, Field3) + ...

Properly set up you could easily do a simple DSum or DLookup into a query to get the real-time value of your record. This means you need to change your table to store each of those 13 values in a new record, not in a different column.

tblQustionResults
qr_ID, autonumber, primary key of table
qr_Respondent, number, foreign key to respondents table to let you know who provided this answer
qr_QuestionNumber, number, number of question responding to
qr_Answer, number, will hold that 1, 2, 3, 88, 998, 999 values you mentioned

Thats it, just 4 fields, not 13. Instead when someone provides 13 answers, they get 13 records in that table. With a structure like that you get to take advantage of the summation functions and simply add together all related values.

 

[moyi]

Dear plog, thanks for the thought. I agree that long table may looks easier to make the query, but it's difficult to put them on a form. Each respondent needs to answer 13 questions (fields), but if I use a long table, I can't guarantee that each respondent will get 13 records.

My take on this is that you have incorrectly set up your table. You keep mentioning 13 fields. My guess is that you literally have a table were one record has a column for each of those 13 values. That's wrong. With that structure you have little choice but to compile a long 13 part equation to get the sum you want:

=Iif(Field1>10, 0, Field1) + Iif(Field2>10, 0, Field2) + Iif(Field3>10, 0, Field3) + ...

Properly set up you could easily do a simple DSum or DLookup into a query to get the real-time value of your record. This means you need to change your table to store each of those 13 values in a new record, not in a different column.

tblQustionResults
qr_ID, autonumber, primary key of table
qr_Respondent, number, foreign key to respondents table to let you know who provided this answer
qr_QuestionNumber, number, number of question responding to
qr_Answer, number, will hold that 1, 2, 3, 88, 998, 999 values you mentioned

Thats it, just 4 fields, not 13. Instead when someone provides 13 answers, they get 13 records in that table. With a structure like that you get to take advantage of the summation functions and simply add together all related values.

 

[bob fitz]

If you don't want to set up the tables as plog suggested then your best solution would will be to use the expression that plog gave you:

=Iif(Field1>10, 0, Field1) + Iif(Field2>10, 0, Field2) + Iif(Field3>10, 0, Field3) + ...

as the control source of a text box on the form. Alternatively, create a query from the table and use the expression as a calculated field in the query. Then use the calculated query field as the record source property for a text box on the form.

but if I use a long table, I can't guarantee that each respondent will get 13 records.

You could restrict the number of records to 13 for each respondent

 

[Pat Hartman]

Since it makes no sense to sum codes, I have to admit to being at a loss as to what you actually want. It must be me because everyone else just jumped in. Maybe it makes sense to them to sum the values contained in Gender, MaritalStatus, and Ethnicity. But it doesn't to me. Obviously I don't know what your questions are so the example may be over the top.

If you are really saying that you want to add fld1 + fld2 + fld3, etc while treating anything other than 1 0r 2 as zero?

As the others have mentioned, your schema is not normalized. While flattening the schema might have made it easier to ensure that all 13 fields contain a value, it is/will make everything else harder. For example, the SQL for a normalized table would be:

Select Sum(AnsVal) as AnsSum From YourTable
WHERE AnsVal <= 2 AND PK = Forms!YourForm!txtPK;

In the long run, it will be far easier for you to create reports and do analysis if you bite the bullet and normalize. Since you want all 13 answered, you can add a field to the parent table to count the answers. In the AfterUpdate event of the subform that manages the answers, use DCount() to count the number of rows with answers for this person. Then Update the count field in the personSurvey record. For your analysis, only select data where the count = 13. You should alsy run a query each time the database opens to look for unfinished data entry.

Granted, adding logic to ensure 13 rows is more difficult than flattening the table but it is the ONLY thing that will be more difficult and when its done, it's done. If you do this correctly and make the "13" a variable associated with a particular survey, the app can handle multiple surveys with different question counts and different questions. You end up with a much more flexible application.

Keep in mind that Access is a relational database, not a spreadsheet. Therefore, it will never have functions that work to sum multiple different columns in a table. Relational databases operate on set theory. That means that every column in a table is a separate attribute and the only set it belongs to is fields of a table. There is no possiblity of a subset. Sets are rows of a table. You use criteria to define which rows belong to a particular set and then your operation operates on ALL rows of the set.

 

[moyi]

Thank you. Do you think it's possible to use loop function in this code?

If you don't want to set up the tables as plog suggested then your best solution would will be to use the expression that plog gave you:

=Iif(Field1>10, 0, Field1) + Iif(Field2>10, 0, Field2) + Iif(Field3>10, 0, Field3) + ...

as the control source of a text box on the form. Alternatively, create a query from the table and use the expression as a calculated field in the query. Then use the calculated query field as the record source property for a text box on the form.You could restrict the number of records to 13 for each respondent

 

[moyi]

I'm thinking to use counting function too. For example, if I can count how many answers equal to 1, how many answers equal to 2, then I can use a variable to calculate the total.

Since it makes no sense to sum codes, I have to admit to being at a loss as to what you actually want. It must be me because everyone else just jumped in. Maybe it makes sense to them to sum the values contained in Gender, MaritalStatus, and Ethnicity. But it doesn't to me. Obviously I don't know what your questions are so the example may be over the top.

If you are really saying that you want to add fld1 + fld2 + fld3, etc while treating anything other than 1 0r 2 as zero?

As the others have mentioned, your schema is not normalized. While flattening the schema might have made it easier to ensure that all 13 fields contain a value, it is/will make everything else harder. For example, the SQL for a normalized table would be:

Select Sum(AnsVal) as AnsSum From YourTable
WHERE AnsVal <= 2 AND PK = Forms!YourForm!txtPK;

In the long run, it will be far easier for you to create reports and do analysis if you bite the bullet and normalize. Since you want all 13 answered, you can add a field to the parent table to count the answers. In the AfterUpdate event of the subform that manages the answers, use DCount() to count the number of rows with answers for this person. Then Update the count field in the personSurvey record. For your analysis, only select data where the count = 13. You should alsy run a query each time the database opens to look for unfinished data entry.

Granted, adding logic to ensure 13 rows is more difficult than flattening the table but it is the ONLY thing that will be more difficult and when its done, it's done. If you do this correctly and make the "13" a variable associated with a particular survey, the app can handle multiple surveys with different question counts and different questions. You end up with a much more flexible application.

Keep in mind that Access is a relational database, not a spreadsheet. Therefore, it will never have functions that work to sum multiple different columns in a table. Relational databases operate on set theory. That means that every column in a table is a separate attribute and the only set it belongs to is fields of a table. There is no possiblity of a subset. Sets are rows of a table. You use criteria to define which rows belong to a particular set and then your operation operates on ALL rows of the set.

 

[bob fitz]

I'm thinking to use counting function too. For example, if I can count how many answers equal to 1, how many answers equal to 2, then I can use a variable to calculate the total.

I'm thinking that you'd be better off in the long run following the advice that was given by plog in post #6

 

[moyi]

Thanks.

Plog's answer is wonderful if my respondents only need to select the number of questions that they want to answer, but in this case, they have to answer all 13 questions. This means I need to set up a rule to ask each of them to create 13 records, which I don't know how to do and make it user friendly.

I think currently using iif function or counting may be a better solution.

I'm thinking that you'd be better off in the long run following the advice that was given by plog in post #6

 

[arnelgp]

what are the fieldnames of these 13 fields?

 

[bob fitz]

Thanks.

Plog's answer is wonderful if my respondents only need to select the number of questions that they want to answer, but in this case, they have to answer all 13 questions. This means I need to set up a rule to ask each of them to create 13 records, which I don't know how to do and make it user friendly.

I think currently using iif function or counting may be a better solution.

Then I wish you luck with your application.

 

[Uncle Gizmo]

ask each of them to create 13 records

I demonstrate a method of creating a list of Records, a list of checkboxes in this video 0 on this page here:- http://www.niftyaccess.com/add-a-check-list-to-your-ms-access-database/ it can be adapted to create a list of text boxes see "Want a Questionnaire in MS Access?" HERE:- http://www.niftyaccess.com/checklist-user-case-examples/ It can further be adapted to offer multi choice, see "Checklist With Multiple Choice" on this page HERE:- http://www.niftyaccess.com/check-list-extras/

Might give you some ideas...

 

[Pat Hartman]

In #9 I gave you a suggestion on how to force the user to enter all 13 answers. You can also work in temp tables so that new surveys are added to a set of temp tables and not added to the main table until all questions are in.

The big problem with flattening the question list is that you are SOL if you have to add a 14th question whereas if you normalize as most of the experts suggest, all that changes is the count of questions that will be required. Spend some time to get your head around it. Requiring n records to be present to complete a set is more difficult to do than most things we offer advice on but it is the correct solution.