Report to Include All Possible Values from CrossTab Query

[Sean Varano]

I have a report that I am trying to create. it summarizes failure/pass on a
particular set of exams for both males and females. I summarized the data
via a crosstab query, and then am trying to create reports. In some
instances, both males and females took the exam, in some cases only males
did. Yet I want to report to include females, but just zero them out. Thus,
I would like the report to include all possible values for both the gender
field and outcome (pass, fail) even if there are not any . Any ideas?

 

[Duane Hookom]

I would create a cartesian query that builds all possible combinations.
Something like:
SELECT [Gender], [Score]
FROM tblGenders, tblScores

Then use the above query to supply column and row headings in your crosstab.
You will need LEFT or RIGHT joins to get all records from the cartesian query.

 

[Sean Varano]

Duane - thanks for the reply. I actually tried that - in a query, hooked
these two definition tables up to the datatable, and then hoped to use them
to set the complete list of options. Problem relates to the necessary join
types. The data table is connected to each definition table via the variable
of choice. Since there are three tables, my understanding is that the join
types have to be type 1 which essentially only includes those options where
there is a valid option in the actual data table. Does that make sense?

I would create a cartesian query that builds all possible combinations.
Something like:
SELECT [Gender], [Score]
FROM tblGenders, tblScores

Then use the above query to supply column and row headings in your crosstab.
You will need LEFT or RIGHT joins to get all records from the cartesian query.

--
Duane Hookom
Microsoft Access MVP

I have a report that I am trying to create. it summarizes failure/pass on a
particular set of exams for both males and females. I summarized the data
via a crosstab query, and then am trying to create reports. In some
instances, both males and females took the exam, in some cases only males
did. Yet I want to report to include females, but just zero them out. Thus,
I would like the report to include all possible values for both the gender
field and outcome (pass, fail) even if there are not any . Any ideas?

 

[Duane Hookom]

This should still be possible. It would help if you provided some table and
field names and SQL of your crosstab.

--
Duane Hookom
Microsoft Access MVP

Duane - thanks for the reply. I actually tried that - in a query, hooked
these two definition tables up to the datatable, and then hoped to use them
to set the complete list of options. Problem relates to the necessary join
types. The data table is connected to each definition table via the variable
of choice. Since there are three tables, my understanding is that the join
types have to be type 1 which essentially only includes those options where
there is a valid option in the actual data table. Does that make sense?

I would create a cartesian query that builds all possible combinations.
Something like:
SELECT [Gender], [Score]
FROM tblGenders, tblScores

Then use the above query to supply column and row headings in your crosstab.
You will need LEFT or RIGHT joins to get all records from the cartesian query.

--
Duane Hookom
Microsoft Access MVP

I have a report that I am trying to create. it summarizes failure/pass on a
particular set of exams for both males and females. I summarized the data
via a crosstab query, and then am trying to create reports. In some
instances, both males and females took the exam, in some cases only males
did. Yet I want to report to include females, but just zero them out. Thus,
I would like the report to include all possible values for both the gender
field and outcome (pass, fail) even if there are not any . Any ideas?

 

[Sean Varano]

Again, thanks Duane. Here is what I have:

the main data table is actually a query that combines some basic
demographica data with outcomes for a particular set of "tests" (physical
exam, medical exam, etc for police recruits). I want to create a report that
summarizes the outcomes by gender. Thus, how many females passed each exam,
how many males. As indicated before, I would like to show a value of "0",
lets say for women, if no women took the exam. Or, lets say, 0 in the fail
for women if they all passed the physical exam.

So, the main data table is named: Qry: Merit and Score Reports
The two definition tables are: tblGender which has values of Male and Female
in the Gender field; and tblOutcomes with has Pass and Fail in the Outcomes
field.

The gender field in the tblGenders table is connected to the "gender" field
in the data query and tblOutcomes is connected to a field named Exam_Med

I hope that helps. Here is the sql:

TRANSFORM Count(tblOutcomes.OUTCOME) AS [Count]
SELECT tblGenders.GENDER
FROM ([QRY: MERIT AND SCORE REPORTS] INNER JOIN tblGenders ON [QRY: MERIT
AND SCORE REPORTS].GENDER = tblGenders.GENDER) INNER JOIN tblOutcomes ON
[QRY: MERIT AND SCORE REPORTS].EXAM_MED = tblOutcomes.OUTCOME
GROUP BY tblGenders.GENDER
PIVOT tblOutcomes.OUTCOME;


Any assistance with this would be greatly appreciated! Best wishes,

This should still be possible. It would help if you provided some table and
field names and SQL of your crosstab.

--
Duane Hookom
Microsoft Access MVP

Duane - thanks for the reply. I actually tried that - in a query, hooked
these two definition tables up to the datatable, and then hoped to use them
to set the complete list of options. Problem relates to the necessary join
types. The data table is connected to each definition table via the variable
of choice. Since there are three tables, my understanding is that the join
types have to be type 1 which essentially only includes those options where
there is a valid option in the actual data table. Does that make sense?

I would create a cartesian query that builds all possible combinations.
Something like:
SELECT [Gender], [Score]
FROM tblGenders, tblScores

Then use the above query to supply column and row headings in your crosstab.
You will need LEFT or RIGHT joins to get all records from the cartesian query.

--
Duane Hookom
Microsoft Access MVP


:

I have a report that I am trying to create. it summarizes failure/pass on a
particular set of exams for both males and females. I summarized the data
via a crosstab query, and then am trying to create reports. In some
instances, both males and females took the exam, in some cases only males
did. Yet I want to report to include females, but just zero them out. Thus,
I would like the report to include all possible values for both the gender
field and outcome (pass, fail) even if there are not any . Any ideas?

 

[Duane Hookom]

I don't see the results of the cartesian query in your crosstab as suggested
in my first reply.
-- qcarGenderOutcomes
SELECT Gender, Outcome
FROM tblGenders, tblOutcomes;

Then use qcarGenderOutcomes as a source in your crosstab and set the links
to you other query to include all the records from qcarGenderOutcomes.
--
Duane Hookom
Microsoft Access MVP

Again, thanks Duane. Here is what I have:

the main data table is actually a query that combines some basic
demographica data with outcomes for a particular set of "tests" (physical
exam, medical exam, etc for police recruits). I want to create a report that
summarizes the outcomes by gender. Thus, how many females passed each exam,
how many males. As indicated before, I would like to show a value of "0",
lets say for women, if no women took the exam. Or, lets say, 0 in the fail
for women if they all passed the physical exam.

So, the main data table is named: Qry: Merit and Score Reports
The two definition tables are: tblGender which has values of Male and Female
in the Gender field; and tblOutcomes with has Pass and Fail in the Outcomes
field.

The gender field in the tblGenders table is connected to the "gender" field
in the data query and tblOutcomes is connected to a field named Exam_Med

I hope that helps. Here is the sql:

TRANSFORM Count(tblOutcomes.OUTCOME) AS [Count]
SELECT tblGenders.GENDER
FROM ([QRY: MERIT AND SCORE REPORTS] INNER JOIN tblGenders ON [QRY: MERIT
AND SCORE REPORTS].GENDER = tblGenders.GENDER) INNER JOIN tblOutcomes ON
[QRY: MERIT AND SCORE REPORTS].EXAM_MED = tblOutcomes.OUTCOME
GROUP BY tblGenders.GENDER
PIVOT tblOutcomes.OUTCOME;


Any assistance with this would be greatly appreciated! Best wishes,

This should still be possible. It would help if you provided some table and
field names and SQL of your crosstab.

--
Duane Hookom
Microsoft Access MVP

Duane - thanks for the reply. I actually tried that - in a query, hooked
these two definition tables up to the datatable, and then hoped to use them
to set the complete list of options. Problem relates to the necessary join
types. The data table is connected to each definition table via the variable
of choice. Since there are three tables, my understanding is that the join
types have to be type 1 which essentially only includes those options where
there is a valid option in the actual data table. Does that make sense?

:

I would create a cartesian query that builds all possible combinations.
Something like:
SELECT [Gender], [Score]
FROM tblGenders, tblScores

Then use the above query to supply column and row headings in your crosstab.
You will need LEFT or RIGHT joins to get all records from the cartesian query.

--
Duane Hookom
Microsoft Access MVP


:

I have a report that I am trying to create. it summarizes failure/pass on a
particular set of exams for both males and females. I summarized the data
via a crosstab query, and then am trying to create reports. In some
instances, both males and females took the exam, in some cases only males
did. Yet I want to report to include females, but just zero them out. Thus,
I would like the report to include all possible values for both the gender
field and outcome (pass, fail) even if there are not any . Any ideas?