TechRepublic|Forums|Software

Software

General discussion

  • Creator
    Topic
  • June 8, 2000 at 4:15 am #2073575

    Access97 Query/Report

    Locked

    by fritz.pyen · about 21 years, 11 months ago

    How do I aggregate percentages in a query? Ultimately I want a report that will show percentages on mulitple levels. I have a table with a City field, a Division field and a Region field. Division is like a business unit that includes multiple cities and Region is country/continent that includes multiple Regions (i.e. Europe, North America etc.).
    i.e. I want to create a report that will show a worldwide percent and then under that, percentages that comprise that worldwide total for North America and Europe. Even under North America and Europe I want break out the sub-percents that comprise those totals, such as Chicago, San Francisco, London, Paris, etc. Currently I only have percents for the lowest level, which are for the cities I justmentioned. Now I just need to know how to total up to North America, Europe and then worldwide.

    Topic is locked This conversation is currently closed to new comments.
  • Creator
    Topic

All Comments

  • Author
    Replies
    • June 9, 2000 at 5:07 am #3776213

      Access97 Query/Report

      by acwiz · about 21 years, 11 months ago

      In reply to Access97 Query/Report

      In you queries create a custom sort order you want using an IIF statement. (example SortRegion:IIF([Region] = “Europe”,1,iif([Region] = “North America”,2,3)) Will give you any record using Europe a sort number of 1 and North America 2 and all other 3. Put all you data in a temporary table (Make Table query) with this custom sort order. Then use groups in your report to get the sort order and the individual breakdowns of percentages.

    • June 12, 2000 at 3:00 am #3778831

      Access97 Query/Report

      by mcthaker · about 21 years, 11 months ago

      In reply to Access97 Query/Report

      Aggregating Percentages is not recommended. You must calculate percentage for each group, eg:
      work out total for each group then average the total for each group rather than trying to average an average – this gives a false calculation. So, you need the underlying figures for the initial percentage figure so that you can total and average them again for each group.

    • September 20, 2000 at 7:04 pm #3754571

      Access97 Query/Report

      by fritz.pyen · about 21 years, 8 months ago

      In reply to Access97 Query/Report

      This question was auto closed due to inactivity

  • Author
    Replies
Viewing 2 reply threads