how to get percentages?

[claudiouk]

Say I have Table A in MS Access 2000 with the following fields: id;
school (humanities, science, medicine) and anxiety(mild, moderate,
severe). I want to end up with the school rows on the left
(humanities, science, medicine) and the anxiety headings on top (mild,
moderate, severe); and the values in the grid being percentages - that
is, each cell value divided by THAT school's total (not the total id
count).

Once I get an idea of how to do this I can add similar fields such as
depression (mild, moderate, severe) etc. to the table. The percentages
will therefore display the proportions of each school affected by a
range of such problems.

Have no idea how to go about doing this - would appreciate any help -
thanks!

 

[Golfinray]

First, in your tables you can right click on any field and get properties for
that individual field. Change it to percentage under format. Then you need a
crosstab query, which will give you the across and down format you are
looking for. If you have problems, write back.

 

[claudiouk]

Hi - when I do a crosstab schoolXanxiety I get the count in the cells
showing the number of students from that school affected by different
levels of anxiety. I can't see an option in the table field property
relating to % - writing percent in the format? But that would
presumably just format the count figure as a %, which would then be
inaccurate. Moreover what I need is to divide the cells by the total
number of students in each school, across all relevant fields - say
anxiety + depression. The crosstab would only deal with one such field
at the time. Basically I first need to find the individual school
totals overall; then do a crosstab such as schoolXanxiety. Then divide
the cells by individual school totals and multiply the result by 100.
But how to combine all these operations?

 

[Golfinray]

Sorry, use a query and right click on aby field to get properties and then
set to percentage. If that won't work you can put in a query field
Formula:[number of students]/[number of problems]. That would get you a
number that you could then multiply by 100. Pull into the query anything you
need, get your percentages, and then run the crosstab from the query.

 

[John Spencer]

Three query solution

Query1
SELECT School, Anxiety, Count(Anxiety) as CountAnxiety
FROM [Table A]
GROUP BY School, Anxiety

Query2
SELECT School, Count(ID) as CountID
FROM [Table A]
GROUP BY School

Query 3
SELECT A.School
, A.Anxiety
, A.CountAnxiety/B.CountID as TheRatio
FROM Query1 as A INNER JOIN Query2 as B
ON A.School = B.School

IF your field and table names consist of only Letters, Numbers, and
underscores - no spaces or punctutation marks, or other characters, then you
can do that all in one query. That would look something like the following
query

SELECT A.School
, A.Anxiety
, A.CountAnxiety/B.CountID as TheRatio
FROM
(
SELECT School, Anxiety, Count(Anxiety) as CountAnxiety
FROM TableA
GROUP BY School, Anxiety
) as A
INNER JOIN
(
SELECT School, Count(ID) as CountID
FROM TableA
GROUP BY School
) as B
ON A.School = B.School


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..