Combo Boxes

[Laurie]

I have a form that has 36 questions each question has a combo box where the
user can either choose yes, no or n/a for each question. I am trying to do a
query to get the total # of yes answers, the total number of no answers and
the total number of N/A answers to each question. The field names are
quest1, quest2 and so on. I tried using =sum(ABS[quest1=-1)) to get the
total number of yes answers but it does not work. Any help would be greatly
appreciated.

 

[KARL DEWEY]

=sum(IIF([quest1]=-1,1,0))

 

[Laurie]

I tried using that formula in my query but it keeps giving me a "1" for each
answer. The overall total did work which is great, but I also need to break
it down into how many "yes", "no" and "N/A" asnwers there were, which did not
work. Not sure what I am doing wrong.

In the table the questions are set up as field name=strquest1, strquest2,
etc. The data type is text. In lookup they are all set up as combo boxes.
The row source type = Table/Query and the row source = Table 3 (which is a
table consisting of yes, no and N/A fields)

The fields used in the query to do a total count of all answers is:
Field=strquest1, ectc., table = table 2 and total = count.

The report I am trying to get would be: Question 1 - total # of answers,
and also a breakdown of # of yes, # of No and # of N/A

Hope my reply is not too confusing. Any help with a way to get a breakdown
of the three different answer sets would be appreciated.

Thank you,

--
Laurie

=sum(IIF([quest1]=-1,1,0))

I have a form that has 36 questions each question has a combo box where the
user can either choose yes, no or n/a for each question. I am trying to do a
query to get the total # of yes answers, the total number of no answers and
the total number of N/A answers to each question. The field names are
quest1, quest2 and so on. I tried using =sum(ABS[quest1=-1)) to get the
total number of yes answers but it does not work. Any help would be greatly
appreciated.[/QUOTE]

 

[KARL DEWEY]

This is the SQL --
SELECT Sum(IIF([strquest1]="Yes", 1, 0)) AS [Question 1 Yes],
Sum(IIF([strquest1]="No", 1, 0)) AS [Question 1 No],
Sum(IIF([strquest1]="N/A", 1, 0)) AS [Question 1 N/A],
Sum(IIF([strquest2]="Yes", 1, 0)) AS [Question 2 Yes],
Sum(IIF([strquest2]="No", 1, 0)) AS [Question 2 No],
Sum(IIF([strquest2]="N/A", 1, 0)) AS [Question 2 N/A], etc...

In design view --
Question 1 Yes: Sum(IIF([strquest1]="Yes", 1, 0))

FROM YourTable;

I tried using that formula in my query but it keeps giving me a "1" for each
answer. The overall total did work which is great, but I also need to break
it down into how many "yes", "no" and "N/A" asnwers there were, which did not
work. Not sure what I am doing wrong.

In the table the questions are set up as field name=strquest1, strquest2,
etc. The data type is text. In lookup they are all set up as combo boxes.
The row source type = Table/Query and the row source = Table 3 (which is a
table consisting of yes, no and N/A fields)

The fields used in the query to do a total count of all answers is:
Field=strquest1, ectc., table = table 2 and total = count.

The report I am trying to get would be: Question 1 - total # of answers,
and also a breakdown of # of yes, # of No and # of N/A

Hope my reply is not too confusing. Any help with a way to get a breakdown
of the three different answer sets would be appreciated.

Thank you,

--
Laurie

=sum(IIF([quest1]=-1,1,0))

I have a form that has 36 questions each question has a combo box where the
user can either choose yes, no or n/a for each question. I am trying to do a
query to get the total # of yes answers, the total number of no answers and
the total number of N/A answers to each question. The field names are
quest1, quest2 and so on. I tried using =sum(ABS[quest1=-1)) to get the
total number of yes answers but it does not work. Any help would be greatly
appreciated.[/QUOTE]

 

[Laurie]

That worked perfectly. Thank you so very much, appreciate all your help!
--
Laurie

This is the SQL --
SELECT Sum(IIF([strquest1]="Yes", 1, 0)) AS [Question 1 Yes],
Sum(IIF([strquest1]="No", 1, 0)) AS [Question 1 No],
Sum(IIF([strquest1]="N/A", 1, 0)) AS [Question 1 N/A],
Sum(IIF([strquest2]="Yes", 1, 0)) AS [Question 2 Yes],
Sum(IIF([strquest2]="No", 1, 0)) AS [Question 2 No],
Sum(IIF([strquest2]="N/A", 1, 0)) AS [Question 2 N/A], etc...

In design view --
Question 1 Yes: Sum(IIF([strquest1]="Yes", 1, 0))

FROM YourTable;

I tried using that formula in my query but it keeps giving me a "1" for each
answer. The overall total did work which is great, but I also need to break
it down into how many "yes", "no" and "N/A" asnwers there were, which did not
work. Not sure what I am doing wrong.

In the table the questions are set up as field name=strquest1, strquest2,
etc. The data type is text. In lookup they are all set up as combo boxes.
The row source type = Table/Query and the row source = Table 3 (which is a
table consisting of yes, no and N/A fields)

The fields used in the query to do a total count of all answers is:
Field=strquest1, ectc., table = table 2 and total = count.

The report I am trying to get would be: Question 1 - total # of answers,
and also a breakdown of # of yes, # of No and # of N/A

Hope my reply is not too confusing. Any help with a way to get a breakdown
of the three different answer sets would be appreciated.

Thank you,

--
Laurie

=sum(IIF([quest1]=-1,1,0))

:

I have a form that has 36 questions each question has a combo box where the
user can either choose yes, no or n/a for each question. I am trying to do a
query to get the total # of yes answers, the total number of no answers and
the total number of N/A answers to each question. The field names are
quest1, quest2 and so on. I tried using =sum(ABS[quest1=-1)) to get the
total number of yes answers but it does not work. Any help would be greatly
appreciated.[/QUOTE]