Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Finding average of top 10%

Status
Not open for further replies.

tonyblack

IS-IT--Management
Apr 3, 2001
9
GB
From the sample query result below i need to be able to report the total average KGs Picked by the top 10%, 50% and 75% of IDs listed.

Picker ID KGs Picked
151319 109.55
150743 107.38
150859 103.55
150841 94.29
150878 90.46
150929 86.63
150839 82.29
151030 79.55
150651 76.29
150393 76.29
150373 70.29
150857 70.29
100100 70.03
100726 69.20

This one's a bit beyond my limited Access skills. Can anyone help?

Tony Black
 
look into the TOP and AVG built in functions

hint:
use F1
 
SELECT TOP 10 PERCENT Avg(Table3.[KGs Picked]) AS [AvgOfKGs Picked]
FROM TableName;
 
You need to create queries to calculate the avaerages and then use dlookup to get them into the form.

For example:(change mytable to your own table name)

QTop50:

SELECT Avg([KGs Picked]) AS AvgK
FROM (SELECT TOP 50 PERCENT Mytable.[kgs picked]
FROM mytable
ORDER BY mytable.[kgs picked]DESC) AS Q1

Then in an unbound textbox set the controlsource as :
=dlookup("avgK","QTop50")

You must do the top x selection first and then get the average of the items selected.
If you try to incorporate the average into the top n query you will always get the same answer as it is averaging all records.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top