Software

General discussion

Gravatar
0 Votes
Locked

Access97 Query/Report

By fritz.pyen ·
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

Access97 Query/Report

by acwiz 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.

gravatar
Collapse -

Access97 Query/Report

by fritz.pyen In reply to Access97 Query/Report

The question was auto-closed by TechRepublic

gravatar
Collapse -

Access97 Query/Report

by mcthaker 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.

gravatar
Collapse -

Access97 Query/Report

by fritz.pyen In reply to Access97 Query/Report

The question was auto-closed by TechRepublic

gravatar
Collapse -

Access97 Query/Report

by fritz.pyen In reply to Access97 Query/Report

This question was auto closed due to inactivity

Back to Software Forum

Start or search

Related Discussions

Related Forums