overall record counts based on conditions

T

TomC

My data base consists of numerous records and each record contains the
answers to 15 questions. Each question could have an answer of "y", "n" or
null. I need an overall record count of the entries spreading the three
possible answers for each of the 15 questions. I'm not knowledgeble of vba.
I am familiar with the IIF function which I have been trying to use in
conjunction with a count(*) but to no avail. So basically, I need to know
how to qualify the count(*) function in the Report Footer Section to get my
totals.
Any help would be appreciated.
Thanks, Tom
 
D

Duane Hookom

If your tables were propery normalized, this might be much easier. I would
probably normalize your table using a union query:
Select 1 As Question, [Q1] as Answer
From tblYourTable
Union All
Select 2, [Q2]
From tblYourTable
Union All
Select 3, [Q3]
From tblYourTable
Union All
......etc....
Select 15, [Q15]
From tblYourTable;

You can save the above as quniNormalized. Then create a query like:
Select Question, Answer, Count(Answer) as NumOf
From quniNormalzed
Group by Question, Answer;
 
T

TomC

Duane, thanks for responding. I should have said that I am also unfamiliar
with how the Select works - sorry. Looking at other questions and responses,
I found one solution that works for the = condition as follows using an
unbound field:
=sum(IIf([Test1]="y",1,0)) This does sum my "y" answers.
Now my only problem is finding out what syntax can be used for summing the
null answers or answers that are not "y" or "n"? My attempts to use the <>
or not= didn't work?????

Duane Hookom said:
If your tables were propery normalized, this might be much easier. I would
probably normalize your table using a union query:
Select 1 As Question, [Q1] as Answer
From tblYourTable
Union All
Select 2, [Q2]
From tblYourTable
Union All
Select 3, [Q3]
From tblYourTable
Union All
......etc....
Select 15, [Q15]
From tblYourTable;

You can save the above as quniNormalized. Then create a query like:
Select Question, Answer, Count(Answer) as NumOf
From quniNormalzed
Group by Question, Answer;

--
Duane Hookom
MS Access MVP


TomC said:
My data base consists of numerous records and each record contains the
answers to 15 questions. Each question could have an answer of "y", "n" or
null. I need an overall record count of the entries spreading the three
possible answers for each of the 15 questions. I'm not knowledgeble of
vba.
I am familiar with the IIF function which I have been trying to use in
conjunction with a count(*) but to no avail. So basically, I need to know
how to qualify the count(*) function in the Report Footer Section to get
my
totals.
Any help would be appreciated.
Thanks, Tom
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You can't use = or <> with Null: Null is never equal to anything.

You either need to use IsNull to determine whether or not you're dealing
with a Null answer, or you can use the Nz function to convert the Null value
to something you can use in an equality comparison.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



TomC said:
Duane, thanks for responding. I should have said that I am also
unfamiliar
with how the Select works - sorry. Looking at other questions and
responses,
I found one solution that works for the = condition as follows using an
unbound field:
=sum(IIf([Test1]="y",1,0)) This does sum my "y" answers.
Now my only problem is finding out what syntax can be used for summing the
null answers or answers that are not "y" or "n"? My attempts to use the
<>
or not= didn't work?????

Duane Hookom said:
If your tables were propery normalized, this might be much easier. I
would
probably normalize your table using a union query:
Select 1 As Question, [Q1] as Answer
From tblYourTable
Union All
Select 2, [Q2]
From tblYourTable
Union All
Select 3, [Q3]
From tblYourTable
Union All
......etc....
Select 15, [Q15]
From tblYourTable;

You can save the above as quniNormalized. Then create a query like:
Select Question, Answer, Count(Answer) as NumOf
From quniNormalzed
Group by Question, Answer;

--
Duane Hookom
MS Access MVP


TomC said:
My data base consists of numerous records and each record contains the
answers to 15 questions. Each question could have an answer of "y", "n"
or
null. I need an overall record count of the entries spreading the
three
possible answers for each of the 15 questions. I'm not knowledgeble of
vba.
I am familiar with the IIF function which I have been trying to use in
conjunction with a count(*) but to no avail. So basically, I need to
know
how to qualify the count(*) function in the Report Footer Section to
get
my
totals.
Any help would be appreciated.
Thanks, Tom
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

=sum(IIf([Test1]="y",1,0))
=sum(IIf([Test1]="n",1,0))
=sum(IIf(IsNull([Test1]),1,0))

I would get fairly tired of this after a couple fields. Normalizing your
data is much better in the long run.

--
Duane Hookom
MS Access MVP


Douglas J. Steele said:
You can't use = or <> with Null: Null is never equal to anything.

You either need to use IsNull to determine whether or not you're dealing
with a Null answer, or you can use the Nz function to convert the Null
value to something you can use in an equality comparison.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



TomC said:
Duane, thanks for responding. I should have said that I am also
unfamiliar
with how the Select works - sorry. Looking at other questions and
responses,
I found one solution that works for the = condition as follows using an
unbound field:
=sum(IIf([Test1]="y",1,0)) This does sum my "y" answers.
Now my only problem is finding out what syntax can be used for summing
the
null answers or answers that are not "y" or "n"? My attempts to use the
<>
or not= didn't work?????

Duane Hookom said:
If your tables were propery normalized, this might be much easier. I
would
probably normalize your table using a union query:
Select 1 As Question, [Q1] as Answer
From tblYourTable
Union All
Select 2, [Q2]
From tblYourTable
Union All
Select 3, [Q3]
From tblYourTable
Union All
......etc....
Select 15, [Q15]
From tblYourTable;

You can save the above as quniNormalized. Then create a query like:
Select Question, Answer, Count(Answer) as NumOf
From quniNormalzed
Group by Question, Answer;

--
Duane Hookom
MS Access MVP


My data base consists of numerous records and each record contains the
answers to 15 questions. Each question could have an answer of "y",
"n" or
null. I need an overall record count of the entries spreading the
three
possible answers for each of the 15 questions. I'm not knowledgeble
of
vba.
I am familiar with the IIF function which I have been trying to use in
conjunction with a count(*) but to no avail. So basically, I need to
know
how to qualify the count(*) function in the Report Footer Section to
get
my
totals.
Any help would be appreciated.
Thanks, Tom
Click to expand...
Click to expand...
Click to expand...
 
T

TomC

Thanks Duane. Now that I know one way of doing it, I promise to look into
the normalization.
Tom

Duane Hookom said:
=sum(IIf([Test1]="y",1,0))
=sum(IIf([Test1]="n",1,0))
=sum(IIf(IsNull([Test1]),1,0))

I would get fairly tired of this after a couple fields. Normalizing your
data is much better in the long run.

--
Duane Hookom
MS Access MVP


Douglas J. Steele said:
You can't use = or <> with Null: Null is never equal to anything.

You either need to use IsNull to determine whether or not you're dealing
with a Null answer, or you can use the Nz function to convert the Null
value to something you can use in an equality comparison.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



TomC said:
Duane, thanks for responding. I should have said that I am also
unfamiliar
with how the Select works - sorry. Looking at other questions and
responses,
I found one solution that works for the = condition as follows using an
unbound field:
=sum(IIf([Test1]="y",1,0)) This does sum my "y" answers.
Now my only problem is finding out what syntax can be used for summing
the
null answers or answers that are not "y" or "n"? My attempts to use the
<>
or not= didn't work?????

:

If your tables were propery normalized, this might be much easier. I
would
probably normalize your table using a union query:
Select 1 As Question, [Q1] as Answer
From tblYourTable
Union All
Select 2, [Q2]
From tblYourTable
Union All
Select 3, [Q3]
From tblYourTable
Union All
......etc....
Select 15, [Q15]
From tblYourTable;

You can save the above as quniNormalized. Then create a query like:
Select Question, Answer, Count(Answer) as NumOf
From quniNormalzed
Group by Question, Answer;

--
Duane Hookom
MS Access MVP


My data base consists of numerous records and each record contains the
answers to 15 questions. Each question could have an answer of "y",
"n" or
null. I need an overall record count of the entries spreading the
three
possible answers for each of the 15 questions. I'm not knowledgeble
of
vba.
I am familiar with the IIF function which I have been trying to use in
conjunction with a count(*) but to no avail. So basically, I need to
know
how to qualify the count(*) function in the Report Footer Section to
get
my
totals.
Any help would be appreciated.
Thanks, Tom
Click to expand...
Click to expand...
Click to expand...
 
Top