Some sort of crosstab query... I think (1 Viewer)

[Howlsta]

Hi,

Someone has asked me to do a bunch of queries I thought they looked easy and they might be, but i've got stuck.

most of the queries are of similar style, i'd like the output to be like this:

[Child Position] [Number] [Percentage]
Front Passenger
Rear-Right
Rear Centre
Rear Left
Unknown
Total 244 100

the number column will be the count of all children in each position. There are 244 children in the DB. Percentage column will obviously show the percentage of children in each position.

Do I have to do more than one query? As number and percentage aren't fields access doesn't seem to allow them in a crosstab, do I have to do a select query first to create them?

childposition is a field in one of my tables it stores numbers but 1 or corresponds to front passenger 3 is rear left etc... I can get a totals query to display all the posibilities and there totals, but how do I get it to but the total for everything on the last line as above?

thanks

Rich

 

[Nouba]

There seems to be no need for a crosstab. Try this

SELECT
  P.[Child Position]
, Count(P.[Child Position]) AS [Number]
, Count([P].[Child Position])/(SELECT
                                 Count(*) FROM tblPosition) AS Percentage
FROM tblPosition AS P
GROUP BY P.[Child Position]
ORDER BY Count(P.[Child Position]);

 

[Howlsta]

Thanks that worked well. I had to manipulate the percentage column slightly i.e. make it 100* bigger - here's the SQL

SELECT P.childposition, Count(P.childposition) AS [Number], Count([P].[ChildPosition])/(SELECT
0.01*Count(*) FROM childinfo) AS [Percent]
FROM childinfo AS P
GROUP BY P.childposition
ORDER BY Count(P.childposition);

I'm fairly sure you can't put the totals on the bottom line, but I guess this could be done on a form or report. Obviously the percentage total will be 100, so no probs.

good stuff, thanks,

Ric


ps didn't need to make that percentage change as when I show it on a form I can simply just change the controls format to percentage.