Report to Include All Possible Values from CrossTab Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sean Varano - 14 Aug 2008 02:52 GMT

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?

Reply to this Message

Duane Hookom - 14 Aug 2008 05:37 GMT

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.

Signature

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
[quoted text clipped - 3 lines]
> 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?

Reply to this Message

Sean Varano - 14 Aug 2008 18:46 GMT

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:
[quoted text clipped - 11 lines]
> > 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?

Reply to this Message

Duane Hookom - 15 Aug 2008 01:56 GMT

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

Signature

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
[quoted text clipped - 19 lines]
> > > 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?

Reply to this Message

Sean Varano - 15 Aug 2008 13:00 GMT

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.
[quoted text clipped - 22 lines]
> > > > 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?

Reply to this Message

Duane Hookom - 15 Aug 2008 17:46 GMT

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.

Signature

Duane Hookom
Microsoft Access MVP

> Again, thanks Duane. Here is what I have:
>
[quoted text clipped - 52 lines]
> > > > > 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?

Reply to this Message