Query problems with Count(IIF( ...&....))

[pauladeanda]

Hi,

I created a query that tells me how many people chose A for question
1, F for question 2, etc... for 25 questions. The query works just
fine. However, the query tells me a total number (considering all of
the records in my database). I have a column in my main table that
records whether a record (student) is a HS student or not (If so, the
column stores a check mark in a box). I want to know if it is
possible to separate the query that I currently have into how many
high school students put A for 1, F for 2, etc... and separately tell
me how many non high school students put A for 1, F for 2, etc....

Is this possible all in one query or do I need to do two? Either
way, I'm stuck on how to actually do that. The code that I currently
have for my query that gives me a total count of each question is...

SELECT Count(IIf([Table1]![SA1]="A",1)) AS CountOf1A,
Count(IIf([Table1]![SA2]="F", 1)) AS Countof2F, ....
FROM Table1;

The table that results looks like the following:

CountOf1A Countof2F Countof3C etc.....
10 11 6

Would it work to do an IIF statement that has two criteria, like
IIF([Table1]![SA1]="A" & [Table1]![HS Student]="yes", 1) ? Is this
how it would be written because when I did this it returned 0 and that
is not correct according to my table.

Hope someone could help!

much appreciative,
Paula

 

[Dirk Goldgar]

Hi,

I created a query that tells me how many people chose A for question
1, F for question 2, etc... for 25 questions. The query works just
fine. However, the query tells me a total number (considering all of
the records in my database). I have a column in my main table that
records whether a record (student) is a HS student or not (If so, the
column stores a check mark in a box). I want to know if it is
possible to separate the query that I currently have into how many
high school students put A for 1, F for 2, etc... and separately tell
me how many non high school students put A for 1, F for 2, etc....

Is this possible all in one query or do I need to do two? Either
way, I'm stuck on how to actually do that. The code that I currently
have for my query that gives me a total count of each question is...

SELECT Count(IIf([Table1]![SA1]="A",1)) AS CountOf1A,
Count(IIf([Table1]![SA2]="F", 1)) AS Countof2F, ....
FROM Table1;

The table that results looks like the following:

CountOf1A Countof2F Countof3C etc.....
10 11 6

Would it work to do an IIF statement that has two criteria, like
IIF([Table1]![SA1]="A" & [Table1]![HS Student]="yes", 1) ? Is this
how it would be written because when I did this it returned 0 and that
is not correct according to my table.

Probably the simplest way to do it would be to leave your current set of
calculated fields alone but GROUP BY the [HS Student] field:

SELECT
[HS Student],
Count(IIf(SA1="A",1)) AS CountOf1A,
Count(IIf([SA2]="F", 1)) AS Countof2F,
....
FROM Table1
GROUP BY [HS Student];

That will return two records, one for high-school students and one for
non-high-school students.

You *could* do it so as to get one record back, defining the
high-school-student counts using similar to your proposed syntax, which was
not quite right:

IIf(SA1]="A" And [HS Student]="yes", 1)

However, using GROUP BY is quicker and simpler, so long as you can accept
receiving multiple records in the result set.

 

[pauladeanda]

Thanks, Dirk, that worked great! I can't believe it was that easy.

thanks again,
Paula

Paula <[email snipped]> wrote in message

Hi,

I created a query that tells me how many people chose A for question
1, F for question 2, etc... for 25 questions. The query works just
fine. However, the query tells me a total number (considering all of
the records in my database). I have a column in my main table that
records whether a record (student) is a HS student or not (If so, the
column stores a check mark in a box). I want to know if it is
possible to separate the query that I currently have into how many
high school students put A for 1, F for 2, etc... and separately tell
me how many non high school students put A for 1, F for 2, etc....

Is this possible all in one query or do I need to do two? Either
way, I'm stuck on how to actually do that. The code that I currently
have for my query that gives me a total count of each question is...

SELECT Count(IIf([Table1]![SA1]="A",1)) AS CountOf1A,
Count(IIf([Table1]![SA2]="F", 1)) AS Countof2F, ....
FROM Table1;

The table that results looks like the following:

CountOf1A Countof2F Countof3C etc.....
10 11 6

Would it work to do an IIF statement that has two criteria, like
IIF([Table1]![SA1]="A" & [Table1]![HS Student]="yes", 1) ? Is this
how it would be written because when I did this it returned 0 and that
is not correct according to my table.

Probably the simplest way to do it would be to leave your current set of
calculated fields alone but GROUP BY the [HS Student] field:

SELECT
[HS Student],
Count(IIf(SA1="A",1)) AS CountOf1A,
Count(IIf([SA2]="F", 1)) AS Countof2F,
....
FROM Table1
GROUP BY [HS Student];

That will return two records, one for high-school students and one for
non-high-school students.

You *could* do it so as to get one record back, defining the
high-school-student counts using similar to your proposed syntax, which was
not quite right:

IIf(SA1]="A" And [HS Student]="yes", 1)

However, using GROUP BY is quicker and simpler, so long as you can accept
receiving multiple records in the result set.