top X Percent

  • Thread starter Thread starter Appelq
  • Start date Start date
A

Appelq

I have a table tblTractorRevenue.
TractorID, Location, Revenue
1234 , 01 , $100
1235 , 01 , $250
.....
5412 , 99 , $125

Each Location has some number of tractors (ex: Location 01 has 30 tractors,
02 has 10, etc.)

I need to list a report, sorted by Location, showing the top 10% revenue
tractors and the Bottom 10 % revenue tractors for Each location)

First, I did a MakeTable query to get the 10% number of tractors for each
location:
Location, Cnt
01 , 30
02 , 10
....
99 , 15

I thought I would write a bit of VB to loop through the table (sorted By
Location, By Revenue DESC) and flag the Top 10% tractors (first 3 for
location 01) and flag the bottom 10% for that location. I have a field in
tblTractorrevenue - TopBottom that can be either "T" or "B" or Null.

Once all of the records are flagged I could then run a query to select *
FROM tblTractorRevenue WHERE TopBottom Not Null and do my thing.

I'm having trouble designing the nested looping structure to do this in VB.

OR is there a better approach to get to my objective?

thanks in advance,
Chuck
 
Use 3 queries.
The one you have now that returns the top 10%
Write another like it that returns the bottom 10 %

Then write a query that joins those two queries and it will return the
records returned by both of the other 2.
 
I will assume that using the Top 10 Percent phrase isn't working for you

SELECT TractorID, Location, Revenue, "TOP" as TopBottom
FROM TblTractorRevenue as A
WHERE TractorID in (
SELECT Top 10 Percent TractorID
FROM tblTractorRevenue As B
WHERE B.Location = A.Location
ORDER BY Revenue Asc)


SELECT TractorID, Location, Revenue, "Bottom" as TopBottom
FROM TblTractorRevenue as A
WHERE TractorID in (
SELECT Top 10 Percent TractorID
FROM tblTractorRevenue As B
WHERE B.Location = A.Location
ORDER BY Revenue DESC)

Third query uses the above queries.
SELECT *
FROM QueryTop
UNION ALL
SELECT *
FROM QueryBottom
ORDER BY Location, TopBottom DESC, Revenue

IF I recall correctly, Top 10 Percent rounds up the number of records to
return (27 records total means it will return 3 top and 3 bottom) Also
top does return TIES in the last position. If you wish to eliminate
ties change the Order by clause in the two subqueries to include the
TractorID.

SELECT TractorID, Location, Revenue, "TOP" as TopBottom
FROM TblTractorRevenue as A
WHERE TractorID in (
SELECT Top 10 Percent TractorID
FROM tblTractorRevenue As B
WHERE B.Location = A.Location
ORDER BY Revenue Asc, TractorID)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top