crosstab query show percentages by row

terrytek

Registered User.
Local time
Today, 03:34
Joined
Aug 12, 2016
Messages
75
Code:
TRANSFORM Count(qryDemogForCrosstab.StudentID) AS CountOfStudentID
SELECT qryDemogForCrosstab.Classification, Count(qryDemogForCrosstab.StudentID) AS [Total Of StudentID]
FROM qryDemogForCrosstab
GROUP BY qryDemogForCrosstab.Classification
PIVOT qryDemogForCrosstab.AgeGroup;
Result is
Total Of Student ID 18-24 25-64 65+ Undefined
ESOL 97 13 82 1 1
Pair 20 1 17 2

which is the expected result.
My question is: How can I get the counts under each age group to display as percentages of the Total Of Student ID per row? I was able to get percentages to show using
Code:
TRANSFORM Formatpercent(Count(qryDemogForCrosstab.StudentID/100,0) AS CountOfStudentID
but this gives me the percentages out of the total of students overall (117) instead of the total per row.
I also tried this:
Code:
TRANSFORM Count(qryDemogForCrosstab.StudentID) AS CountOfStudentID
SELECT qryDemogForCrosstab.Classification, Count(qryDemogForCrosstab.StudentID) AS [Total Of StudentID],
   Count(qryDemogForCrosstab.StudentID)/(Select Count(*) FROM qryDemogForCrosstab) AS Percentage
FROM qryDemogForCrosstab
GROUP BY qryDemogForCrosstab.Classification
PIVOT qryDemogForCrosstab.AgeGroup;
but I get an error "Multi level GROUP BY clause is not allowed in a subquery."
 
You can't get both ,totals and percents , in 1 query.
You need 1 query to count, and 1 qry to calc percent.

You could use append queries to write both to a 'report' table.
The percent fields would be formatted as %.
 

Users who are viewing this thread

Back
Top Bottom