Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2007
    Posts
    14

    Unanswered: percentage from dcount

    I'm using this formula in a report footer to obtain the count of scores
    greater than 3.
    =DCount("[RhymeBOYK]","qryTPRIKinderBOYCurrentYear","[RhymeBOYK]>3")

    I then need to know the percentage of students who scored over 3 out of the total of Count([StuID]).

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Instead of using DCount, I might elect to do an expression in the query for the report - ie. CountOfScores3: IIF([RhymeBOYK]>3,1,0) and then add the CountOfScores3 field to the report (making it invisible) and simply sum the field in the report footer. (note: if any of the [RhymeBOYK] values are null, you may have to modify the IIF expression to test for null otherwise you'll get an error.) ex: IIF(isnull([RhymeBOYK]),0,IIF([RhymeBOYK]>3,1,0)) but don't quote me if I got the syntax 100% correct.

    To get the percentage of students who scored over 3, also have another expression in the query - TotCount: 1 and again, then sum that field in the report footer. Then to get the percentage of students who scored over 3, simply add a field (in the report footer with your summed fields) and take the name of the field which summed the CountofScores3 and divide that by the name of the field which summed the TotCount field (multiplied by 100).
    Last edited by pkstormy; 04-15-08 at 20:46.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I would do it the way PK suggested.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Sep 2007
    Posts
    14

    counting and percentages

    Thank you for your help! I've got the first part working but I'm having trouble with the percentage. I did the TotCount: 1 and summed that in the footer of my report but the default name "Text87" (I also tried changing the name in the properties window) doesn't work. The report is trying to find "text87" and it can't. I can get the totals but I can't find the right names to use in the percent formula...



    Quote Originally Posted by pkstormy
    Instead of using DCount, I might elect to do an expression in the query for the report - ie. CountOfScores3: IIF([RhymeBOYK]>3,1,0) and then add the CountOfScores3 field to the report (making it invisible) and simply sum the field in the report footer. (note: if any of the [RhymeBOYK] values are null, you may have to modify the IIF expression to test for null otherwise you'll get an error.) ex: IIF(isnull([RhymeBOYK]),0,IIF([RhymeBOYK]>3,1,0)) but don't quote me if I got the syntax 100% correct.

    To get the percentage of students who scored over 3, also have another expression in the query - TotCount: 1 and again, then sum that field in the report footer. Then to get the percentage of students who scored over 3, simply add a field (in the report footer with your summed fields) and take the name of the field which summed the CountofScores3 and divide that by the name of the field which summed the TotCount field (multiplied by 100).

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you've assigned the value of somethign to a control, when you do that Access uses the string / text datatype

    so to do your calcualtions you need to covnvert it back to a number


    eg
    =cint([<mynumberofitems>])/cint([<mytotalitems>]))*100

  6. #6
    Join Date
    Sep 2007
    Posts
    14
    Quote Originally Posted by healdem
    so you've assigned the value of somethign to a control, when you do that Access uses the string / text datatype

    so to do your calcualtions you need to covnvert it back to a number


    eg
    =cint([<mynumberofitems>])/cint([<mytotalitems>]))*100
    My problem is that the name I am using in the formula where you have [<mynumberofitems] says it can't find it.

    I did the expressions in my query. I got the correct sum of each expression in the report footer. But, when I tried to use the expression names in the percent equation in the report footer, it can't find the field (expression) names.

    I tried =sum([CountOfScoresRhyme])/([CountofStuID])

    When I try to view the report, it pops up a box that ask what each of those names are.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I got the correct sum of each expression in the report footer.
    Since you have this, you can at least use the contents of each. So instead of referring to the controls that these are in, use the expressions inside them.

    =Sum(CountOfScores3)/Sum(TotCount) for example.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by cbeavers
    My problem is that the name I am using in the formula where you have [<mynumberofitems] says it can't find it.

    I did the expressions in my query. I got the correct sum of each expression in the report footer. But, when I tried to use the expression names in the percent equation in the report footer, it can't find the field (expression) names.

    I tried =sum([CountOfScoresRhyme])/([CountofStuID])

    When I try to view the report, it pops up a box that ask what each of those names are.

    yeah I'm noit surprised... <somethingorother> is a documentation convention to suggest you replace <somehtingorother> with a valid varaiabel or value

    say your text box conating the number of students in your example and was called NumberofStudents, then you'd replace <somethingorother> with NumberofStudents

  9. #9
    Join Date
    Sep 2007
    Posts
    14
    Quote Originally Posted by StarTrekker
    Since you have this, you can at least use the contents of each. So instead of referring to the controls that these are in, use the expressions inside them.

    =Sum(CountOfScores3)/Sum(TotCount) for example.
    Thank you! Thank you! Thank you! That worked!!!

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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