Crosstab confusion - how to choose column titles

[train2]

Basic explanation of situation:

I have classes with pupils. For each pupil I have 3 fields (current, target, future) that can take values 1-8 (for levels). I would like a report that counts the number of pupils at each level for each of current, target and future for each class.

To do a crosstab query I have to define the column headings (using one of current, target and future). The row is always Class, and the count is always Pupil. That all works fine.

Now i'd like to combine the results from these 3 queries into one, and also I'd like to see all the levels (1-8) in the columns headings, even if there is no count for that level. Any ideas?

 

[johnnymac43]

Do you want three columns for each value, or only one?

To force a column for each value you will need to resort to 'dummy' data. If your crosstab is to show counts, and you have one 'dummy' pupil for each column, you can simply subtract 1 from each count to get the true counts for your data.

HTH

John