Xtab query to show percentage, aaaaarrrrrgggggghhh (1 Viewer)

[Howlsta]

Hi folks,

here is my SQL

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

at the moment this SQL gets the count, I want to do another a query which instead of the count will show percentage, I tried:

TRANSFORM Count([childinfo].[agechild])/(SELECT
Count(*) FROM childinfo) AS [Percent]
SELECT childinfo.chilRestraint
FROM childinfo
GROUP BY childinfo.chilRestraint
PIVOT childinfo.agechild;

it produces the following error "multi-level group by clause not allowed in a sub query"

is there any other way to get the percentages in there. Hopefully can crack it by tomorrow been stuck for a while.

any help appreciated as ever,

Rich


I've come up with an idea of how to do it now, I have created another query based on the xtab and am gonna add another field adjacent to each age range. I want to work out the percentages e.g. in the field description something like this:

19-24: nz([19-24mths]/([0-9 mths]+[10-11 yrs]+[10-18 mths]+[11-12 yrs]+[19-24mths]+[2-3 yrs]+[3-4 yrs]+[4-5 yrs]+[5-6 yrs]+[6-7 yrs]+[8-9 yrs]+[7-8 yrs]+[9-10 yrs]+[N/A]))

the problem i am having now is that some of the fields are null, and the calculation is not being done. I've put an nz in because I would like the null's to be read as zero, doesn't work though; have I got it in the correct place???

 

[Howlsta]

seems I have to put nz in front of every reference to a field name:

19-24: (nz([19-24mths])/(nz([0-9 mths])+nz([10-11 yrs])+nz([10-18 mths])+nz([11-12 yrs])+nz([19-24mths])+nz([2-3 yrs])+nz([3-4 yrs])+nz([4-5 yrs])+nz([5-6 yrs])+nz([6-7 yrs])+nz([8-9 yrs])+nz([7-8 yrs])+nz([9-10 yrs])+nz([N/A])))

a bit fiddly but there it is