Results 1 to 13 of 13
  1. #1
    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. #2
    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)
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  3. #3
    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. #4
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Could you display

    your SQL that you allready have?
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  5. #5
    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. #6
    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;
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  7. #7
    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. #8
    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. #9
    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. #10
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450
    Quote 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)
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  11. #11
    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. #12
    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!
    Attached Files Attached Files

  13. #13
    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
  •