Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Odd grouping levels (Access 2002)

    Hi All!

    I have a query that pulls regional and national figures by activity category that results in something like this:
    Activity Regional Hours % National Hours %
    Sales Calls 1000 50 5000 50
    Sales Paperwork 500 25 2000 20
    Admin 500 25 3,000 30
    Leave 500 20 2,000 20

    If the %'s don't line up right, it's because they're as a % of everything EXCEPT leave.

    What I need to do is set up grouping levels so that they come out so I can do this:

    Activity Regional Hours % National Hours %
    Sales Calls 1000 50 5000 50
    Sales Paperwork 500 25 2000 20

    Total Sales 1500 75 7,000 70
    Admin 500 25 3,000 30
    Total Core Hours 2000 100 10,000 100

    Leave 500 2,000
    Total Hours 2,500 12,000

    What I've done so far is create a calculated grouping level, Activity Line, that if the activity is Sales, then it's group 1, for admin it's group 2, then for leave it's group 3. Then I made a running sum in the Activity Line group.

    Somehow, my totals don't total. It SHOULD be just a matter of summing, but the percents don't add up properly. Instead of Total Sales summing to 75, it's summing to 60 (which is way too big to be a rounding error).

    If I can get past this hurdle, I'm sure the rest is just a matter of not displaying the % column when Activity="Leave" (I hope).

    Has anyone had this problem before?

    TIA,

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

    Re: Odd grouping levels (Access 2002)

    Are you saying your query returns this:


    <table border=1><td>Activity </td><td>Regional Hours </td><td>%</td><td>National Hours </td><td>%</td><td>Sales Calls </td><td>1000</td><td>50</td><td>5000</td><td>50</td><td>Sales Paperwork </td><td>500 </td><td>25 </td><td>2000 </td><td>20</td><td>Admin </td><td>500 </td><td>25 </td><td>3,000 </td><td>30</td><td>Leave </td><td>500 </td><td>20 </td><td>2,000 </td><td>20</td></table>

    And your totals should come out like this:

    <table border=1><td>Totals</td><td>Regional Hours</td><td>%</td><td>National Hours </td><td>%</td><td>Total Sales </td><td>1500 </td><td>75</td><td>7,000 </td><td>70</td><td>Admin </td><td>500 </td><td>25 </td><td>3,000</td><td>30</td><td>Total Core Hours</td><td>2000 </td><td>100</td><td>10,000 </td><td>100</td><td>Leave </td><td>500 </td><td> </td><td>2,000 </td><td> </td><td>Total Hours</td><td>2,500</td><td> </td><td>12,000 </td><td> </td></tr><tr></table>


    You can't just add the percentages, you have to calculate the new percentages using a different denominator. 1500/2000 is 75%, but 1500/2500 is 60%. You used the overall total as the denominator for the original percentages so it's still part of the equation when you add them up.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Odd grouping levels (Access 2002)

    Hi Charlotte:

    Actually, the problem I had was a rounding error. My calculations were based on the correct number (I don't know if the totals looked wrong, but all the other %'s were of core hours, including leave), but it really was a rounding error. I had percentages in the query, then when I went to sum things on the report, it was _completely_ dropping what was after the decimal point.

    The solution? In my query, instead of Percentage as a type, I times'd everything by 100 so it would display as a full % with unlimited decimal places. Then when I added it all in the report, I also added Round(Sum([My Field]),0) so the totals would come out to 100% of core.

    As for the leave category, I just set the textbox to not display if we're in the category that includes leave.

    Sorry for the subject of this being wrong. I was going to delete my post, but then I thought it might be useful to leave it because of the percentage lesson.

Posting Permissions

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