# Thread: complex percentage problem

1. Registered User
Join Date
Oct 2004
Posts
6

## Unanswered: complex percentage problem

Hi I'm new here and fairly new with Access too and using MS Access 2000. I've got TABLE A with fields ID, SCHOOL (Humanities, Science, Medicine), ANXIETY (mild, moderate, severe) and DEPRESSION (mild, moderate, severe). A crosstab query with school as rows and anxiety as colums would result in counts in the grid. I want these to be percentages - not in terms of total count of IDs, but of the totals for each school in turn. In other words for each school adding up the row values for both anxiety and depression = 100%. This way one can compare proportions of students in different schools affected by different conditions. I can do a simple crosstab and can get the list of school totals but then don't know how to proceed to output the percentages (also formatted without the %). Any help would be very appreciated.

2. Registered User
Join Date
Jul 2003
Location
Amsterdam, Nederland
Posts
450

## re

Do a count and a sum of the count

so your result will be something like

row 1 : school A - 13 - 90
row 2 : school B - 12 - 90
row 3 : school C - 33 - 90
row 4 : school D - 32 - 90

Now dont show them in your grid but calc it
Or in code or in sql it self
(13/90)*100
result will be
row 1 : school A - (13 / 90)*100 = 14,44
row 2 : school B - (12 / 90)*100 = 13,33
row 3 : school C - (33 / 90)*100 = 36,66
row 4 : school D - (32 / 90)*100 = 35,55

Round the result and you'll have 100% as total (just past a %-sign at the end)

3. Registered User
Join Date
Oct 2004
Posts
6

## re more complex percentage problem

Thank you for responding Greetz. Maybe putting some figures might help here. Imagine the combined crosstabs yield the following COUNTS (of IDs):
...........................DEPRESSION............. ..ANXIETY.............TOTAL
SCHOOL......mild...moderate..severe..mild..moderat e...severe.........
Humanities....3.........5............2.........8.. .....2............3........23
Science........4.........1............1 ........2........2............3........13
Medicine.......2.........6............1.........6. .......6...........1........22
.................................................. ................................... ___
.................................................. .....................................58
What I want is to substitute these counts with percentages - such that in each row the count*100 is divided by the total for the school concerned.
eg Humanities: 3*100/23, 5*100/23 etc; for Science: 4*100/13, 1*100/13 etc; for Medicine: 2*100/22, 6*100/22 etc

Could you show me the appropriate SQL statement to insert in a query (as I really am quite a beginner!) ? Many thanks.

4. Registered User
Join Date
Jul 2003
Location
Amsterdam, Nederland
Posts
450

## Could you display

your SQL that you allready have?

5. Registered User
Join Date
Oct 2004
Posts
6

## the SQL so far..

I've fiddled around in design view and obtained this that seems to work for Depression, up to the "count" stage. I've done a similar one for Anxiety. Thereafter attempts to include a query with school totals and using those in an expression have failed because I always don't get some syntax or something right!

TRANSFORM Count(tableA.id) AS [The Value]
SELECT tableA.schools, Count(tableA.id) AS Totals
FROM tableA
WHERE (((tableA.schools) Is Not Null) AND ((tableA.depression) Is Not Null))
GROUP BY tableA.schools
PIVOT tableA.depression;

6. Registered User
Join Date
Jul 2003
Location
Amsterdam, Nederland
Posts
450

## quick one to check

Does this work

can imagine that the inner query is produced first
and then the Transform bit

ain't got no Acces here only SQL server

TRANSFORM Count(tableA.id) / Totals * 100 AS [The Value]
SELECT tableA.schools, Count(tableA.id) AS Totals
FROM tableA
WHERE (((tableA.schools) Is Not Null) AND ((tableA.depression) Is Not Null))
GROUP BY tableA.schools
PIVOT tableA.depression;

7. Jaded Developer
Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Provided Answers: 59
is this on a report.. if so Id suggest doing the maths in the report itself, you don't have to to do all the maths in one SQL you know....

create text boxes on the report in the detail section
in the report format event do your percentage calcaulation and assign the value of that to each text box as required. you would probably have to trap for zero or null in the incoming data.

in the reports on format event
tbPCentMild.text = ([mild]/[total])*100

where this approach may fall down is if you need to look at the percentages in the column, rather than the row, but you can get round that by findign the total in the column in a group header (using a bit of SQL to grab those totals and then work down the column as each detail comes in.)

arguably the same can be done on a form, using code in the on current event.

8. Registered User
Join Date
Oct 2004
Posts
6
Hi, thanks - we're getting closer.. I thought
TRANSFORM Count(tableA.id) / Totals * 100 AS [The Value] should be
TRANSFORM 100*Count(tableA.id) / Totals AS [The Value] no?
But either way I get wrong answers:

............DEPRESSION (%) from tableA should be:
SCHOOL ....mild....moderate....severe ...total D
humanities...13..........22............9.........4 3
science.......31...........8.............8........ 46
medicine.......9..........27............5......... 41
........................but instead get:
humanities...30..........50............20........1 0
medicine......22..........67............11........ .9
science.......67..........17............17........ .6

I think the problem still is that the denominator for each school should not be the total of that school for depression only (10,6,9), but the overall count (of id's) for each school (23, 13 and 22) - see original crosstab I presented.

Don't think doing it via reports is less time consuming than analyzing the crosstab in Excel and getting the percentages for each row there. The problem is that the figures here are just examples. I have to obtain the percentages for dozens and dozens of crosstabs with 10 schools X half a dozen parameters. Hence my quest for automation via Access!!

So how can we get the overall school totals to be the denominators in the calculation of % for each row cell?

Thanks again for your help Marvels & Healden..

9. Jaded Developer
Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Provided Answers: 59
if you have hundreds and hundreds... and its a report.. give the coding in the report a try.. it wont take to long to come to a conclusion.

10. Registered User
Join Date
Jul 2003
Location
Amsterdam, Nederland
Posts
450
Originally Posted by claudiouk
.............A crosstab query with school as rows and anxiety as colums would result in counts in the grid. .

Just to clear a thing up for me.
Do you want your result in a Grid ; Report ; Excell or just an query

And are you able/aloud to make code(vba)

11. Jaded Developer
Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Provided Answers: 59
if you are still struggling Id suggestr you post the db here (suitably sanitised of course) and I'm happy to have a look at it

12. Registered User
Join Date
Oct 2004
Posts
6

## here's the test database

Thanks Marvels & Healdem - I've put the mdb file in a zip 'cause it wasn't among acceptable extensions. This has the simple example figures mentioned before.

Regarding code - don't understand it but have copied and pasted code from internet sources to enhance a form I created.

All I really need is a qyery-like table of figures so I can copy the figures and paste them into existing Word tables which I re-use year after year as they're already formatted as I want them. I have at least 30 such tables at present and currently I have to analyse the queries in Excel to obtain percentages, row by row -rather time-consuming. I've done some nice forms in the past but no reports.

I would have thought it straightforward to obtain a schools total query that gives total students per school. And then include that data in a crosstab schoolsXanxiety (or depression etc) so that the percentages are calculated by the respective school totals. Whether this can be done in design view or SQL or code, whichever way would be fine..

THANKS AGAIN!

13. Registered User
Join Date
Oct 2004
Posts
6

## is this microsoft example relevant?

http://support.microsoft.com/kb/256282/en-us

even this though may not deal with different school size denominators in calculating % in cells?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•