Query pull based on percents

  • Thread starter Thread starter FedWerkker
  • Start date Start date
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,
 
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
 
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
 
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
 
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;
 
Back
Top