TOP 10 SELECT SUBQUERY

[geckodfw]

Perhaps this is a new issue.

I have tried and failed. This is the SQL within my Access DB.

SELECT qry_top10_region.acct_name, qry_top10_region.acct_name, qry_top10_region.region, qry_top10_region.recv_acct_id, qry_top10_region.balamt, qry_top10_region.over60
FROM qry_top10_region;

What I need from Access is the 10 account names (acct_name) with the highest over60 for each region. Help!

 

[tlbroadbent]

I'm not really sure what you need because you didn't tell what the result of qry_top10_region is. I would guess it summarizes data from a table or tables. If this is so, have you tried the TOP predicate with a descending sort in your query?

SELECT TOP 10
q.acct_name, q.acct_name,
q.region, q.recv_acct_id,
q.balamt, q.over60
FROM qry_top10_region As q
ORDER BY q.Over60 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.

 

[geckodfw]

I have a table of about 6000 records with a field called 'territory'. The 'territory' consists of values like "SW", "SW1", "SW2", "NE","NE1","NE2". I made a query called qry_top10_region from this table that creates a 'region' field combining all the "SW*" fields into the 'region' as "Southwest", and all the "NE*" fields as "Northeast" for example.

I have made another query based on the 'qry_top10_region'. From this query I need to get the 10 highest "Southwest", the 10 highest "Northeast", etc. based on a field called "over60"

Here is the SQL again from the second query.

SELECT qry_top10_region.acct_name, qry_top10_region.acct_name, qry_top10_region.region, qry_top10_region.recv_acct_id, qry_top10_region.balamt, qry_top10_region.over60
FROM qry_top10_region;

All I have done at this point is pull all of the fields I need into this query.

Thanks again.

 

[tlbroadbent]

The following should work for you. Just copy and paste it into a new query. You may want to use recv_acct_id as the WHERE criteria rather than acct_name. I'm not sure which is better.

NOTE: I eliminated the duplicates selection of acct_name from the query.

SELECT
q.acct_name,
q.region,
q.recv_acct_id,
q.balamt,
q.over60
FROM qry_top10_region As q
WHERE q.acct_name IN
(SELECT TOP 10 acct_name
FROM qry_top10_region
WHERE region=q.region
ORDER BY over60 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.