Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating Percent (Access XP)

    I'm trying to do something that I think is very simple, but it just isn't coming out right. I suspect in the two years since I worked in Access with any consistency, I've forgotten something basic, but it is eluding me.

    I have a report that is tabulating the results of a survey. The data in the report is based on this query:

    SELECT tblQuestions.QID, tblAnswers.AnsID, tblResults.Answer, Count(tblResults.AnsID) AS [Count], tblQuestions.QText, tblAnswers.AnsText
    FROM (tblQuestions INNER JOIN tblResults ON tblQuestions.QID = tblResults.QID) INNER JOIN tblAnswers ON (tblResults.AnsID = tblAnswers.AnsID) AND (tblQuestions.QID = tblAnswers.QID)
    GROUP BY tblQuestions.QID, tblAnswers.AnsID, tblResults.Answer, tblQuestions.QText, tblAnswers.AnsText
    ORDER BY tblQuestions.QID;

    The report just lists each question in a Section Header followed by a Detail section that lists each answer and the total number of responses for each (it gets a little trickier when we hit the "fill in the blank" questions, but that's basically it). The counts are working correctly, as is the total count of answers for that question in the Section Footer. But I can't seem to get a percent calculation to work. I've tried [Count]/[txtSum] and [Count]/Sum([Count]), each of which gives an answer, but the wrong one. In case it helps shed some light, below is an example of what I'm getting with the second formula ([Count]/Sum([Count]). Red is in the Header, green the Detail, and blue the Footer.

    <pre><font color=red> What is the name of your Home Office?
    Count Percent Answer</font color=red>
    <font color=448800>2 0.37% Albuquerque
    6 0.37% Bremerton
    3 0.37% Corporate
    1 0.37% Corvallis
    1 0.37% Denver
    26 0.37% Kirkland
    3 0.37% Olympia
    13 0.37% Portland
    2 0.37% Seattle
    9 0.37% Sumner
    1 0.37% Tacoma</font color=448800>
    <font color=blue>67</font color=blue> </pre>

    With the second formula, every answer comes out .37% as shown here, no matter how many total responses there were for that section. If it helps, there are 4,297 total answers. I thought the percents might be based on that total somehow, but I couldn't see a correlation. (But, then, I can NEVER just "see" a relationship between numbers!)

    With the first formula ([Count]/[txtSum]), I get 23.88% when there are 67 responses. A question with 7 responses gets 228.57%, 64 answers gets 25%, 4 answers get 400%, etc. It is consistent based on the number of responses.

    What I want is that 38.8% of the responders are in Kirkland, 13.4% in Sumner, etc. Please shed some light. . .

    Many thanks!

    --Karyl <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating Percent (Access XP)

    First, I suggest you change Count as a name to AnswerCount or something else. Count is a method in VBA, so that is not a good choice for an expression name. Second, calculate the total answers, either in your query or in a DCount function in the Report Open event, and use that value to calculate your percentages. Otherwise, I suspect you wind up using only the total of answers so far in each section, instead of the overall total.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Percent (Access XP)

    Now THAT'S a mistake I've made before, inadvertently using a function name as a field name and running into trouble! But it was a LONG time ago, probably in Access 4.0--too long ago to recognize it today when it happened again. I didn't even think of it! Changing the field name to AnsCount, as you predicted, fixed the problem like magic. And since I WANTED the calculation to be based on the totals in each section, not the overall total, I didn't have to change anything else.

    Many, many thanks! Now back to see if I can remember how to limit the report to only those responders meeting certain characteristics. . .

    --Karyl <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

Posting Permissions

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