Zeros in a crosstab query

[Guest]

I have a crosstab query which analyses examination results. For a given
subject, the query counts the number of students with each grade (A, B, C,
etc) (the columns) in each of a number of schools (BW800, BW801, etc) (the
rows). If there are no students in a particular grade at a school, I use the
nz function to report zero. If there are no students for the subject at a
school, each cell in the row should contain zero, and I need the row to be
reported. However, in the event, such a row is not returned.

What can I do to get all rows returned regardless of the contents of the
cells - even when all the values are zero.

Any help would be much appreciated - as ever.

Jim Jones

 

[Duane Hookom]

Use a join that includes every school.

 

[Guest]

Dear Duane

Thanks for the response. Can you help me a bit more. The query is based on
a single table which contains the School Identifier (BW800, BW801, etc) as
the first field. How do I set up " join which includes every school".

Sorry about this! Thanks again!

Jim Jones

 

[Duane Hookom]

I can't imagine a system that doesn't have a table with every unique School
Identifier and the associated school name etc. However, if you don't have
one, you can create a group by/totals query that does the same thing.

SELECT [SchoolIdentifier]
FROM tblYourTable
GROUP BY [SchoolIdentifier];