Top 20 Account By Branch

[zyus]

I hv this sample data

LNAMT ACNO BRANCH
10,000 123 00100
20,000 124 00200
40,000 125 00100
100,000 126 00300...........

In a query, how to list Top 20 accounts (with highest LNAMT) segregrated by
each branch, meaning each branch will list their Top 20 accounts.

Thanks

 

[Allen Browne]

In a query, how to list Top 20 accounts (with highest LNAMT) segregrated
by each branch, meaning each branch will list their Top 20 accounts.

See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

 

[zyus]

I've read the query but dont have the idea to incorporate my field name in
the query....

 

[Allen Browne]

That will depend on your primary key.

If you don't have a primary key field, add an autonumber so you do.

 

[Bwion]

Hey Allen,

I am having a similar problem. First, my table structure:
AircraftID
AircraftType
SN
DN
Customer
DeliveryDate
WorkorderNum
CategoryID

Alright, here is the output I want. I want the top 10 aircraft in each of
the following groups.

AircraftType = 737
CategoryID = 1

AircraftType = 737
CategoryID = 2

AircraftType = 737
CategoryID = 3

AircraftType = 747
CategoryID = 1

etc, etc, etc.

Basically, I want to group the aircraft by type and then category and then
take the top 10 of those aircraft in each group.

It doesn't quite fit the example on your site since everything is located in
one table. Any tips/advice/code?

Thanks,

Ben

 

[Bwion]

Hey Allen,

I forgot to mention. I need the top 10 ordered by delivery date (i.e. the
last ten aircraft delivered).

Thanks,

Ben

 

[Allen Browne]

See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

 

[Bwion]

Hey Allen,

I saw the site, but I am still having trouble. It shows how to take the TOP
n record per group if they are located in different tables. All my data is in
one table. What I need is the last five deliveried aircraft per each
AircraftType and Category. I don't really know how to apply your code to my
table structure, which is why I asked for further assistance. Thanks in
advance.

Ben

 

[John Spencer]

Simplest method uses a correlated subquery. This is not always the FASTEST
method, but it is the simplest.

SELECT A.*
FROM YourTable As A
WHERE A.DeliveryDate
IN (SELECT TOP 10 B.DeliveryDate
FROM YourTable as B
WHERE B.AirCraftType = A.AirCraftType
AND B.CategoryID = A.CategoryID
ORDER BY B.DeliveryDate DESC)

Make sure you have indexes on DeliveryDate, AirCraftType and CategoryID to get
the best speed out of this.

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

 

[Bwion]

Hey John,

I see that will work for one particular aircraft type and category
combination, but I need to return all the combinations. Is there a way to
embed multiple select statements (i.e. WHERE A.DeliveryDate IN
(SELECT......), (SELECT.....))? I don't know the correct syntax or if it is
even possible.

I would rather not have to hard code all the combinations cause then if a
new combination comes up I will have to change the query, but it may be the
only way. Any thoughts?

Thanks,

Ben

 

[John Spencer]

DID you try the query? It should give you the top ten for each aircrafttype
and category combination.

You don't hard code any combinations the query should automatically take care
of that.

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

 

[Bwion]

Hey John,

Forgive for being hasty! It works perfectly. I really appreciate your help.

Thanks,

Ben

 

[zyus]

Hi John,

I still hv some problem to sort Top 20 LNAMT for each branch as per my
sample data below

LNAMT ACNO BRANCH
10,000 123 00100
20,000 124 00200
40,000 125 00100
100,000 126 00300....

I used access for data analysis. I hv only one table and more than 400K
records and there's no primary key in my table..I can make a query to limit
20 top values but unable to group 20 top values by each branch..

I saw allen site but still having problem. Hope you could help

 

[John Spencer]

YOU might TRY the following query. I think it will work

SELECT A.Branch, A.ACNO, A.LNamt
FROM [YourTable] As A LEFT JOIN [YourTable] As B
ON A.Branch = B.Branch
AND A.LNamt > B.LNamt
GROUP BY A.Branch, A.ACNO, A.LNamt
HAVING Count(B.Branch) <= 19

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

 

[zyus]

Thanks for your response.

I've tried the query but the query is still running more than 3 hours till
now...

What could be the problem....

YOU might TRY the following query. I think it will work

SELECT A.Branch, A.ACNO, A.LNamt
FROM [YourTable] As A LEFT JOIN [YourTable] As B
ON A.Branch = B.Branch
AND A.LNamt > B.LNamt
GROUP BY A.Branch, A.ACNO, A.LNamt
HAVING Count(B.Branch) <= 19

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

Hi John,

I still hv some problem to sort Top 20 LNAMT for each branch as per my
sample data below

LNAMT ACNO BRANCH
10,000 123 00100
20,000 124 00200
40,000 125 00100
100,000 126 00300....

I used access for data analysis. I hv only one table and more than 400K
records and there's no primary key in my table..I can make a query to limit
20 top values but unable to group 20 top values by each branch..

I saw allen site but still having problem. Hope you could help

 

[John Spencer]

Make sure you have indexes on Branch and LNamt fields.

It could be the volume of records you are processing. Or it could be a bad
query on my part.

You might try an experiment. Create and save a query that returns records for
just one branch and ACNo.

Replace the references to YourTable with references to the query. See if that
runs. If it does then the query is correctly constructed and the problem is
the volume of records, especially if you have not indexed at least branch and
LNamt. Indexing ACNo may also help.

SELECT A.Branch, A.ACNO, A.LNamt
FROM [QueryName] As A LEFT JOIN [QueryName] As B
ON A.Branch = B.Branch
AND A.LNamt > B.LNamt
GROUP BY A.Branch, A.ACNO, A.LNamt
HAVING Count(B.Branch) <= 19

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

Thanks for your response.

I've tried the query but the query is still running more than 3 hours till
now...

What could be the problem....

YOU might TRY the following query. I think it will work

SELECT A.Branch, A.ACNO, A.LNamt
FROM [YourTable] As A LEFT JOIN [YourTable] As B
ON A.Branch = B.Branch
AND A.LNamt > B.LNamt
GROUP BY A.Branch, A.ACNO, A.LNamt
HAVING Count(B.Branch) <= 19

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

 

[John Spencer]

When I run a similar query against a table with 2.2 million records it takes
approximately 10 to 12 minutes to execute in SQL server. I haven't tested it
against that large a table in Access.

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

Make sure you have indexes on Branch and LNamt fields.

It could be the volume of records you are processing. Or it could be a
bad query on my part.

You might try an experiment. Create and save a query that returns
records for just one branch and ACNo.

Replace the references to YourTable with references to the query. See
if that runs. If it does then the query is correctly constructed and
the problem is the volume of records, especially if you have not indexed
at least branch and LNamt. Indexing ACNo may also help.

SELECT A.Branch, A.ACNO, A.LNamt
FROM [QueryName] As A LEFT JOIN [QueryName] As B
ON A.Branch = B.Branch
AND A.LNamt > B.LNamt
GROUP BY A.Branch, A.ACNO, A.LNamt
HAVING Count(B.Branch) <= 19

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

Thanks for your response.

I've tried the query but the query is still running more than 3 hours
till now...

What could be the problem....

YOU might TRY the following query. I think it will work

SELECT A.Branch, A.ACNO, A.LNamt
FROM [YourTable] As A LEFT JOIN [YourTable] As B
ON A.Branch = B.Branch
AND A.LNamt > B.LNamt
GROUP BY A.Branch, A.ACNO, A.LNamt
HAVING Count(B.Branch) <= 19

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

 

[John Spencer]

Ok, I couldn't help myself.

I tested the query from an Access DB linked via ODBC to the SQL server. It
failed the first time with an ODBC TimeOut error. I then changed the ODBC
timeout of the query to zero (no timeout) and ran the query again. It took
688 seconds (11.3 minutes) to run an returned the expected results.

I guess the next thing I will try is to test this against a jet db copy of the
sql table. By the way, I did have indexes on the relevant fields.

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

When I run a similar query against a table with 2.2 million records it
takes approximately 10 to 12 minutes to execute in SQL server. I
haven't tested it against that large a table in Access.

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

Make sure you have indexes on Branch and LNamt fields.

It could be the volume of records you are processing. Or it could be a
bad query on my part.

You might try an experiment. Create and save a query that returns
records for just one branch and ACNo.

Replace the references to YourTable with references to the query. See
if that runs. If it does then the query is correctly constructed and
the problem is the volume of records, especially if you have not
indexed at least branch and LNamt. Indexing ACNo may also help.

SELECT A.Branch, A.ACNO, A.LNamt
FROM [QueryName] As A LEFT JOIN [QueryName] As B
ON A.Branch = B.Branch
AND A.LNamt > B.LNamt
GROUP BY A.Branch, A.ACNO, A.LNamt
HAVING Count(B.Branch) <= 19

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

Thanks for your response.

I've tried the query but the query is still running more than 3 hours
till now...

What could be the problem....


:

YOU might TRY the following query. I think it will work

SELECT A.Branch, A.ACNO, A.LNamt
FROM [YourTable] As A LEFT JOIN [YourTable] As B
ON A.Branch = B.Branch
AND A.LNamt > B.LNamt
GROUP BY A.Branch, A.ACNO, A.LNamt
HAVING Count(B.Branch) <= 19

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

 

[John Spencer]

Could not resist - I had to test this with a jet database. I built a table
(2.2 million records) in an Access JET database and ran the query against
various numbers of records. I limited the records by using a query to select
a number of records based on criteria. I used this stored query to test with.

The following results were obtained by running the query once and then
changing the number of records. They could be affected by record caching and
other things happening on my computer. However, they should give you a feel
for what to expect.

If the table had N records, the query took N seconds
54K : 9
99K : 13
138K : 19
176K : 27
212K : 37
248K : 50
317K : 82
383K : 115

On the last test each member of the group had from 4 to 50 records - heavily
weighted towards having 50 records in the group.

I think that eventually, I would run into memory constraints and would have
Access using the hard drive as a memory store while running this. I ran this
with Access 2003 SP1, 1 Gig of RAM, 3 GHz CPU.

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

Ok, I couldn't help myself.

I tested the query from an Access DB linked via ODBC to the SQL server.
It failed the first time with an ODBC TimeOut error. I then changed the
ODBC timeout of the query to zero (no timeout) and ran the query again.
It took 688 seconds (11.3 minutes) to run an returned the expected results.

I guess the next thing I will try is to test this against a jet db copy
of the sql table. By the way, I did have indexes on the relevant fields.

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

When I run a similar query against a table with 2.2 million records it
takes approximately 10 to 12 minutes to execute in SQL server. I
haven't tested it against that large a table in Access.

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

Make sure you have indexes on Branch and LNamt fields.

It could be the volume of records you are processing. Or it could be
a bad query on my part.

You might try an experiment. Create and save a query that returns
records for just one branch and ACNo.

Replace the references to YourTable with references to the query.
See if that runs. If it does then the query is correctly constructed
and the problem is the volume of records, especially if you have not
indexed at least branch and LNamt. Indexing ACNo may also help.

SELECT A.Branch, A.ACNO, A.LNamt
FROM [QueryName] As A LEFT JOIN [QueryName] As B
ON A.Branch = B.Branch
AND A.LNamt > B.LNamt
GROUP BY A.Branch, A.ACNO, A.LNamt
HAVING Count(B.Branch) <= 19

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

zyus wrote:
Thanks for your response.

I've tried the query but the query is still running more than 3
hours till now...

What could be the problem....


:

YOU might TRY the following query. I think it will work

SELECT A.Branch, A.ACNO, A.LNamt
FROM [YourTable] As A LEFT JOIN [YourTable] As B
ON A.Branch = B.Branch
AND A.LNamt > B.LNamt
GROUP BY A.Branch, A.ACNO, A.LNamt
HAVING Count(B.Branch) <= 19

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

 

[zyus]

I have replaced my table with a query for one branch and 70 records and
within split of seconds i've got the result...

When i open the limit to 27000 records for one branch it run more than 15
minutes (and i stop the query)

FYI i'm using Access 2003 and my total records is only 400,000.

I havent do the indexing...coz i'm curious whether will there be any major
changes to my data base when i do indexing?

Thanks

Make sure you have indexes on Branch and LNamt fields.

It could be the volume of records you are processing. Or it could be a bad
query on my part.

You might try an experiment. Create and save a query that returns records for
just one branch and ACNo.

Replace the references to YourTable with references to the query. See if that
runs. If it does then the query is correctly constructed and the problem is
the volume of records, especially if you have not indexed at least branch and
LNamt. Indexing ACNo may also help.

SELECT A.Branch, A.ACNO, A.LNamt
FROM [QueryName] As A LEFT JOIN [QueryName] As B
ON A.Branch = B.Branch
AND A.LNamt > B.LNamt
GROUP BY A.Branch, A.ACNO, A.LNamt
HAVING Count(B.Branch) <= 19

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

Thanks for your response.

I've tried the query but the query is still running more than 3 hours till
now...

What could be the problem....

YOU might TRY the following query. I think it will work

SELECT A.Branch, A.ACNO, A.LNamt
FROM [YourTable] As A LEFT JOIN [YourTable] As B
ON A.Branch = B.Branch
AND A.LNamt > B.LNamt
GROUP BY A.Branch, A.ACNO, A.LNamt
HAVING Count(B.Branch) <= 19

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