Crosstab question

[David R]

Okay, so if I build a crosstab query out of a query that joins two tables, HOW do I get it to fill in the blanks? For instance:

I want to collect Proficiency data per Student. So I have tableStudents, tableProficiencies (reference) and tableStudentProficiencies (many-to-many table). Each student gets their proficiencies listed and a 1,2,3 for Proficiency level.

Now say for any given student, I've only got 4 proficiencies out of 7 covered so far. So when I run my crosstab, there are blanks in the other three fields for that student. Since the missing ones are different for each student (potentially), I've set the ColumnHeadings as fixed. Now do I show something other than a blank in the ones they don't have yet?

Nz() doesn't work, because there's no entry in the table to even check for Null. Iif() doesn't work, apparently for the same reason. I can't even get the report controls to show a "N/A" without filling my tableStudentProficiencies with dummy values.

There has to be a better way. What am I missing?

 

[Jake]

Unless you're trying to address this problem through the crosstab query itself, one option would be to fill out the non-values before using the crosstab:

Query1: Add tables "tableStudents" and "tableProficiencies", unjoined. List (assumedly) StudentID and ProficiencyID. Each student will have one record per Proficiency.

Query2: Add tables "Query1" and "tableStudentProficiencies", joined on StudentID and ProficiencyID. List StudentID, ProficiencyID, and another field which returns 0 if ProficiencyLevel (from "tableStudentProficiencies") is Null, otherwise return ProficiencyLevel.

Crosstab: Build the crosstab based on Query2. All Students now have a value for each Proficiency.

 

[Rich]

Why not set the Default values for each to 0

 

[David R]

Rich: Because they still won't have 0 if there's no record existing for that combination.

Envision this: 17 students in the class, 7 proficiencies. If Student 1 has exposure to proficiencies 1, 2, 4, 5, 6, then they will have no value in 1,3 and 1,7. Jake's solution makes an interesting use of cartesian products, but it gives the correct results. He neglected to mention that they were RIGHT JOINS however in query2.

[This message has been edited by David R (edited 05-01-2002).]

 

[Jake]

Quite right about the joins in Query2, forgot to clarify that slightly important detail.