Query pull based on percents

F

FedWerkker

MS Access 2003

Here's what I need to do:
I have a Table with fields: Office and Value
I need to sum up Value Grouped by Office and provide the detail records
(that are from highest to lowest order) that comprise 80% of Summed(Value)
(also based on Office).

I'm racking by brain. It should be possible. Has anyone had to do this
before? If so, I'd really appreciate some insight in how to do it.

thanks,
 
B

Ben

that comprise 80% of Summed(Value)
do you mean, for example, if you have 10 different values: 80% *
(1+2+3+4+5+6+7+8+9+10), that you only wants values that is 80% greater
or equal to the 44?

Ben
 
F

FedWerkker

Ben,

1
2
3
4
5
6
7
8
9
10
==
55 80% or 44


I'd need the 10, 9, 8, 7, 6 = 40 (it has to less than or = to 44)
what mgt wants to do is research the top 80% transactions by office
and so on for the next Office

hope this helps
 
B

Ben

This is not tested, but you can try something like this:

Assume you have a table called test2

test2
Office value
1 10
2 20
3 50
4 60
5 30
6 40
7 70
8 45
9 85
10 90



Create 3 queries, call them as follow:

Query3
SELECT Sum(value) AS SumOfvalue
FROM test2;


Query2
SELECT Office, value
FROM test2
GROUP BY Office, value;


Query4
SELECT top 80 percent
Query2.Office,
Query2.value,
Query3.SumOfvalue,
value/SumOfvalue AS PercentOfTotal
FROM Query2, Query3
order by value/SumOfvalue



Ben
 
K

KARL DEWEY

I think query 4 needs this change --
Query4
SELECT top 80 percent
Query2.Office,
Query2.value,
Query3.SumOfvalue,
value/SumOfvalue AS PercentOfTotal
FROM Query2, Query3
order by value/SumOfvalue DESC;
 
Top