Complex Crosstab with grouping... arrrgghhh

[Howlsta]

Hi Everyone! How are you all today?

I've been doing some queries and have cracked most of them, the last one's may have to be crosstabs, please take a look at this example:

________________________MAIS________________
CHILD RESTRAINT______0___________1_________>2
Infant carrier
Booster Cushion
etc...

so as the rows will represent the 12 different restraints.
MAIS is a field in the same table as CHILDRESTRAINT. I have a function to calculate the MAIS group which has been tested(below).
If we go across the table the top left box will store the COUNT of the number of accidents where the child was in an infant carrier and the MAIS was 0. The next one across will be the count where restraint was infant carrier and MAIS was 1 etc...

Public Function AISGroup(AIS)

Select Case AIS

Case 0
AISGroup = "0"
Case 1
AISGroup = "1"
Case Is >= 2
AISGroup = ">=2"

End Select

End Function

I can't seem to get the layout right to produce the table I want, I think including the grouping makes it harder. I think I need a crosstab, but I certainly can't seem to be able to use the wizard to do it! arggh.

thanks for looking

Rich

 

[Howlsta]

It seems not as complicated as I thought. I built a crosstab with the wizard, it made me select a third field though, but I managed to modify it in design view to get rid of that field and modify another one. The final SQL was:

TRANSFORM Count(childinfo.HighestAIS) AS [The Value]
SELECT childinfo.chilRestraint
FROM childinfo
GROUP BY childinfo.chilRestraint
PIVOT AISGroup([HighestAIS]);

Looks okay at the moment!

The only other thing is I wanted to include the COUNT and then the percentage in adjacent columns for each MAIS. It seems you can only have one value though in a crosstab and I guess the percentages would have to be displayed as values as well. Does anyone know about this?

Rich