TOP 10 Per region 1

[geckodfw]

I have been making more attempts at this query, and I still haven't any results yet.

I have a table consisting of 7753 records. The records consist of 3 fields, REGION, ACCT, and AMOUNT.

I need to query this table to pull out the 10 highest amounts for each region. There are 9 different regions, so I should have 90 records that the query brings back.

To recap:
I have table INFO consisting of records that contain REGION, ACCT, and AMOUNT. and I need the top 10 from each of the 9 different REGION values.

Thanks in advance.

 

[THoey]

Can you post your current query? Terry M. Hoey

 

[geckodfw]

SELECT
top10regions.region,
top10regions.recv_acct_id,
top10regions.acct_name,
top10regions.conv_amt,
top10regions.over60
FROM top10regions
ORDER BY top10regions.region, top10regions.over60 DESC;

That's the query I have so far, just pulling all 7753 records.

 

[BoxHead]

G,

I thought this would have been a simple UNION query, but I did some autofill in Excel, imported a table like yours into Access '97 and found it extremely frustrating.

I only worked with Top 3 accounts in 9 regions and had assumed that the following would have worked.

SELECT TOP 3 Top3OfNine.Amount, Top3OfNine.Region, Top3OfNine.Account
FROM Top3OfNine
WHERE (((Top3OfNine.Region)=1))
ORDER BY Top3OfNine.Amount DESC , Top3OfNine.Region;
Union
SELECT TOP 3 Top3OfNine.Amount, Top3OfNine.Region, Top3OfNine.Account
FROM Top3OfNine
WHERE (((Top3OfNine.Region)=2))
ORDER BY Top3OfNine.Amount DESC , Top3OfNine.Region;
Union
   etc....

The only way I was able to get the results you need was by saving the individual queries as qryRegion1, qryRegion2, etc and then building a Union query:

TABLE qryregion1
union
Table qryRegion2
union
Table qryRegion3
 etc....
ORDER BY Region;

This did give the top three accounts in the nine regions, but I don't know if it will work for you. Obviously, it isn't very flexible. It will adapt to new data but not new regions.

Hopefully it will suffice, or someone will be able to take it to the next step in VBA.


John

Use what you have,
Learn what you can,
Create what you need.

 

[tlbroadbent]

Try something like the following.

Select Region, Acct, Amount
From Info As i
Where Acct In
(Select Top 10 Acct
From Info Where Region=i.Region
Order By Amount Desc) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[BoxHead]

Terry,

Dot MY i!

That's a beautiful thing. Any pointers to more info on aliases like this?
_____________________________________

Gecko,

Be aware that the Select Top 10 will return more than ten records if there is a tie for 10th place.

______________________________________ John

Use what you have,
Learn what you can,
Create what you need.

 

[tlbroadbent]

John,

I'm not sure what you mean by "Any pointers to more info on aliases like this?" Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[BoxHead]

Terry,

I don't want to take over Gecko's thread with my questions. It's just that in trying to answer the original question, I had tried a couple variations of IN clauses. I got an error telling me to use the EXISTS reserved word, made some changes with that but could only get either all 128 records from my sample table, the top three, or 128 blank records. I think now that this was because I didn't have the table "i" to use in the subqueries Where clause.

This is something I don't know about. Do you have any suggestions where I could learn more?


Thanks,
John

Use what you have,
Learn what you can,
Create what you need.

 

[tlbroadbent]

I suppose there are a number of books and articles that discuss nested subqueries in some detail. Here are a few articles.

http://msdn.microsoft.com/library/en-us/office97/html/output/F1/D2/S5A337.asp

http://msdn.microsoft.com/library/en-us/dnima00/html/ima0056.asp

http://support.microsoft.com/support/kb/articles/q208/9/46.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[geckodfw]

I ran into the same problem. When I try to use the "AS" part of the suggestion, Access redefines the table I had based the query on as an empty table called "I". From there it won't work. I am going to peruse the suggestions you gave.

Thanks.

 

[geckodfw]

Ok
I have this:
SELECT q.acct_name, q.region, q.recv_acct_id, q.conv_amt, q.over60
FROM top10regions AS q
WHERE (((q.acct_name) In (SELECT TOP 10 acct_name FROM top10regions WHERE region=q.region ORDER BY over60 Desc)));

in Access, right now it's returning 610 records, 122 from the first region, 37 from the second, and so on... I'm terribly confused.

 

[tlbroadbent]

My guess is that the Acct_Name must occur multiple times. The assumptin of the query is that the Acct_name (or other column used) will be unique within region. Do you have a unique value for each record or do you need to summarize the data by Region/Acct_Name before selecting the TOP 10? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[BoxHead]

Gecko,

What does the following return?

SELECT Top 10 Top10Regions.conv_amt, Top10Regions.recv_acct_id, Top10Regions.acct_name, Top10Regions.region
WHERE Top10Regions.region =

"the name/number of one of your regions"


Also, what is

Top10Regions.over60

?


John

Use what you have,
Learn what you can,
Create what you need.

 

[BoxHead]

Sorry, G,

I left the order by out of the last post.

Should be:

SELECT Top 10 Top10Regions.conv_amt, Top10Regions.recv_acct_id, Top10Regions.acct_name, Top10Regions.region
WHERE Top10Regions.region = "the name/number of one of your regions"
ORDER BY Top10regions.conv_amt;

John

Use what you have,
Learn what you can,
Create what you need.

 

[geckodfw]

Here is what finally worked.

SELECT p1.region, p1.acct_name, p1.recv_acct_id, p1.conv_amt, p1.over60
FROM top10regions AS p1
WHERE p1.over60 in (select top 10 p2.over60 from top10regions as p2 where p2.region = p1.region order by p2.over60 desc);

It takes forever, but it does return what I want. Thanks for everyone's help!

 

[tlbroadbent]

Make sure you have an index on Region and perhaps even on Acct to help boost performance. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Aivars]

Look at thread705-102904

Aivars