Query Set Up for Null Results

G

Guest

I am using Office Access 2003. I am having a problem when the final Query C
below displays no data when one of the subqueries have no results. Issue: I
have 143 individuals that completed a survey in the database. For Question 4
of the survey, an individual can choose either: none, some or all of the 7
responses, by checking Yes to a “Yes/No†formatted field in each of the 7
fields. Of these 7 individual responses, I want the Count of only the Yes
responses. Query A below “qryQ4 Purpose†is the initial query. Subquery B
is the sample of the first field [Q4TV] in which 62 individuals responded
“Yes†to [Q4TV]. No problem. I have used the same format for each of the 7
fields; only the Yes responses. [Q4TV] through [Q4O]. However, for one of
the fields, no-one has chosen any Yes responses to [Q4R]. So, all 143
individuals indicated No. If this happens, I want the subquery to give me a
0 (zero) result; however the subquery gives me no results. Thus for the
summary SubQuery D below, when I select all the [CountOf…] fields for each of
the 7 separate subqueries into 1 Query, the end result provides no result,
because just one of those subqueries has a null answer. I have several
Questions in the survey that will have this same situation. My question, how
do I build the subqueries to provide a 0 when no Yes responses are found?

Query A: “qryQ4 Purposeâ€
SELECT [tblTHistory A Q1 Q9].[THQID#], [tblTHistory A Q1 Q9].Q4TV,
[tblTHistory A Q1 Q9].Q4WD, [tblTHistory A Q1 Q9].Q4VF, [tblTHistory A Q1
Q9].Q4MV, [tblTHistory A Q1 Q9].Q4R, [tblTHistory A Q1 Q9].Q4RE, [tblTHistory
A Q1 Q9].Q4O, FROM [tblTHistory A Q1 Q9];

Subquery B: “qryQ4 Purpose 1TVâ€
SELECT [qryQ4 Purpose].Q4TV, Count([qryQ4 Purpose].Q4TV) AS CountOfQ4TV
FROM [qryQ4 Purpose]
GROUP BY [qryQ4 Purpose].Q4TV
HAVING ((([qryQ4 Purpose].Q4TV)=Yes));

SubQuery C: “qryQ4 Purpose Summaryâ€
SELECT [qryQ0 Count of Surveys].[CountOfTHQID#], [qryQ4 Purpose
1TV].CountOfQ4TV, [qryQ4 Purpose 2WD].CountOfQ4WD, [qryQ4 Purpose
3VF].CountOfQ4VF, [qryQ4 Purpose 4MV].CountOfQ4MV, [qryQ4 Purpose
5R].CountOfQ4R, [qryQ4 Purpose 6RE].CountOfQ4RE, [qryQ4 Purpose 7O].CountOfQ4O
FROM [qryQ4 Purpose 1TV], [qryQ4 Purpose 2WD], [qryQ4 Purpose 3VF], [qryQ4
Purpose 4MV], [qryQ4 Purpose 5R], [qryQ4 Purpose 6RE], [qryQ4 Purpose 7O],
[qryQ0 Count of Surveys];

Your help would be greatly appreciated. Thank you. Curtis…..
 
J

Jeff Boyce

Curtis

Also check to see if your joins in your underlying queries are
"directional". Are you only returning data when there's a match, or are you
returning all data from one table, and any that matches from the other(s)?

Regards

Jeff Boyce
<Office/Access MVP>
 
G

Guest

Jeff: Thanks for your responses. 1st, I did look at HooKom Library, and it
seems there are helpful hints there, however, I couldn't pinpoint the answer
I was looking for. 2nd response for clarification, I didn't have any in
joins in my query. However, I did get the results I wanted by altering the
first query and using just the first query for my final query to get the
results I needed. Below are the two queries:

SELECT [tblTHistory A Q1 Q9].[THQID#], [tblTHistory A Q1 Q9].Q4TV,
IIf([Q4TV]=True,1,0) AS Q4TVYes, [tblTHistory A Q1 Q9].Q4WD,
IIf([Q4WD]=True,1,0) AS Q4WDYes, [tblTHistory A Q1 Q9].Q4VF,
IIf([Q4VF]=True,1,0) AS Q4VFYes, [tblTHistory A Q1 Q9].Q4MV,
IIf([Q4MV]=True,1,0) AS Q4MVYes, [tblTHistory A Q1 Q9].Q4R,
IIf([Q4R]=True,1,0) AS Q4RYes, [tblTHistory A Q1 Q9].Q4RE,
IIf([Q4RE]=True,1,0) AS Q4REYes, [tblTHistory A Q1 Q9].Q4O,
IIf([Q4O]=True,1,0) AS Q4OYes, [tblTHistory A Q1 Q9].Q4OSpecify
FROM [tblTHistory A Q1 Q9];

SELECT Sum([qryQ4 Purpose].Q4TVYes) AS SumOfQ4TVYes, Sum([qryQ4
Purpose].Q4WDYes) AS SumOfQ4WDYes, Sum([qryQ4 Purpose].Q4VFYes) AS
SumOfQ4VFYes, Sum([qryQ4 Purpose].Q4MVYes) AS SumOfQ4MVYes, Sum([qryQ4
Purpose].Q4RYes) AS SumOfQ4RYes, Sum([qryQ4 Purpose].Q4REYes) AS
SumOfQ4REYes, Sum([qryQ4 Purpose].Q4OYes) AS SumOfQ4OYes
FROM [qryQ4 Purpose];


So in my final results I get 62, 86, 22, 1, 0, 2, 2

Thanks. Curtis....

Curtis said:
I am using Office Access 2003. I am having a problem when the final Query C
below displays no data when one of the subqueries have no results. Issue: I
have 143 individuals that completed a survey in the database. For Question 4
of the survey, an individual can choose either: none, some or all of the 7
responses, by checking Yes to a “Yes/No†formatted field in each of the 7
fields. Of these 7 individual responses, I want the Count of only the Yes
responses. Query A below “qryQ4 Purpose†is the initial query. Subquery B
is the sample of the first field [Q4TV] in which 62 individuals responded
“Yes†to [Q4TV]. No problem. I have used the same format for each of the 7
fields; only the Yes responses. [Q4TV] through [Q4O]. However, for one of
the fields, no-one has chosen any Yes responses to [Q4R]. So, all 143
individuals indicated No. If this happens, I want the subquery to give me a
0 (zero) result; however the subquery gives me no results. Thus for the
summary SubQuery D below, when I select all the [CountOf…] fields for each of
the 7 separate subqueries into 1 Query, the end result provides no result,
because just one of those subqueries has a null answer. I have several
Questions in the survey that will have this same situation. My question, how
do I build the subqueries to provide a 0 when no Yes responses are found?

Query A: “qryQ4 Purposeâ€
SELECT [tblTHistory A Q1 Q9].[THQID#], [tblTHistory A Q1 Q9].Q4TV,
[tblTHistory A Q1 Q9].Q4WD, [tblTHistory A Q1 Q9].Q4VF, [tblTHistory A Q1
Q9].Q4MV, [tblTHistory A Q1 Q9].Q4R, [tblTHistory A Q1 Q9].Q4RE, [tblTHistory
A Q1 Q9].Q4O, FROM [tblTHistory A Q1 Q9];

Subquery B: “qryQ4 Purpose 1TVâ€
SELECT [qryQ4 Purpose].Q4TV, Count([qryQ4 Purpose].Q4TV) AS CountOfQ4TV
FROM [qryQ4 Purpose]
GROUP BY [qryQ4 Purpose].Q4TV
HAVING ((([qryQ4 Purpose].Q4TV)=Yes));

SubQuery C: “qryQ4 Purpose Summaryâ€
SELECT [qryQ0 Count of Surveys].[CountOfTHQID#], [qryQ4 Purpose
1TV].CountOfQ4TV, [qryQ4 Purpose 2WD].CountOfQ4WD, [qryQ4 Purpose
3VF].CountOfQ4VF, [qryQ4 Purpose 4MV].CountOfQ4MV, [qryQ4 Purpose
5R].CountOfQ4R, [qryQ4 Purpose 6RE].CountOfQ4RE, [qryQ4 Purpose 7O].CountOfQ4O
FROM [qryQ4 Purpose 1TV], [qryQ4 Purpose 2WD], [qryQ4 Purpose 3VF], [qryQ4
Purpose 4MV], [qryQ4 Purpose 5R], [qryQ4 Purpose 6RE], [qryQ4 Purpose 7O],
[qryQ0 Count of Surveys];

Your help would be greatly appreciated. Thank you. Curtis…..
Click to expand...
 
Top