Crosstab - adding a subtotal (additional totals)

[sxschech]

Here is an example of a crosstab query that provides counts by gender and location and also will provide an overall total and also subtotals all the genders too.

TRANSFORM Sum(ACC) AS SumOfACC
SELECT 2 AS SortOrder, Fall, LVL, "Accepted" AS Status, Sum(ACC) AS Total, Sum(IIf([Sex]="F",([ACC]),0)) AS Female, Sum(IIf([Sex]="M",([ACC]),0)) AS Male
FROM [YOUR_TABLE]
WHERE (((lvl)="F" Or (lvl)="T") AND ((Section)<>"PS"))
GROUP BY 1, Fall, lvl
ORDER BY Fall, lvl, 1
PIVOT IIf(IsNull(ETHNIC),"9",ETHNIC) & IIf(IsNull(sex),"9",sex);

Partial Sample Header listing for visuals, so numbers may not total. Also, couldn't get cols to line up, sorry...
[tt]
SortOrder Fall LVL Status Total Female Male 1F 1M 9F 9M BF BM FF FM
2 20005 B Accepted 475 236 239 125 39 75 0 36 75 0 125
[/tt]