Top 20 Account By Branch

[John Spencer]

Applying indexes should not cause a problem. It should significantly speed up
the operation of the query.

I continued to experiment and was able to process a table of a million records
in about 17 minutes. A table of slightly over 400,000 records took roughly 2
minutes 15 seconds to process.

The time will vary depending on how many records how are involved and how many
records are in each group that needs to be processed.

So my advice is add indexes.

Also, I have not tested this over a network using an Access (Jet) database.
So that may also be having an significant effect on your performance.

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

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

 

[zyus]

Hi John,

I've indexed all the fields and when i run it on 2-3 branches i can get the
result not more than a minute....But when i open the query to all branches i
will take more than an hour . My total records is around 350,000 and i have
14 branches. Each branch will have an average of 25000 records. I'm running
the query on access database which reside on my pc. I'm using Access 2003.

Thanks

Applying indexes should not cause a problem. It should significantly speed up
the operation of the query.

I continued to experiment and was able to process a table of a million records
in about 17 minutes. A table of slightly over 400,000 records took roughly 2
minutes 15 seconds to process.

The time will vary depending on how many records how are involved and how many
records are in each group that needs to be processed.

So my advice is add indexes.

Also, I have not tested this over a network using an Access (Jet) database.
So that may also be having an significant effect on your performance.

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

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

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

 

[u]

Hi John,

I've indexed all the fields and when i run it on 2-3 branches i can get
the
result not more than a minute....But when i open the query to all branches
i
will take more than an hour . My total records is around 350,000 and i
have
14 branches. Each branch will have an average of 25000 records. I'm
running
the query on access database which reside on my pc. I'm using Access 2003.

Thanks

Applying indexes should not cause a problem. It should significantly
speed up
the operation of the query.

I continued to experiment and was able to process a table of a million
records
in about 17 minutes. A table of slightly over 400,000 records took
roughly 2
minutes 15 seconds to process.

The time will vary depending on how many records how are involved and how
many
records are in each group that needs to be processed.

So my advice is add indexes.

Also, I have not tested this over a network using an Access (Jet)
database.
So that may also be having an significant effect on your performance.

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

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

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

 

[u]

Hi John,

I've indexed all the fields and when i run it on 2-3 branches i can get
the
result not more than a minute....But when i open the query to all branches
i
will take more than an hour . My total records is around 350,000 and i
have
14 branches. Each branch will have an average of 25000 records. I'm
running
the query on access database which reside on my pc. I'm using Access 2003.

Thanks

Applying indexes should not cause a problem. It should significantly
speed up
the operation of the query.

I continued to experiment and was able to process a table of a million
records
in about 17 minutes. A table of slightly over 400,000 records took
roughly 2
minutes 15 seconds to process.

The time will vary depending on how many records how are involved and how
many
records are in each group that needs to be processed.

So my advice is add indexes.

Also, I have not tested this over a network using an Access (Jet)
database.
So that may also be having an significant effect on your performance.

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

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

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

 

[John Spencer]

Well, this could be a case for using a temporary work table (in a temporary
database) to store the results and to loop through the branches one at a time
and append the results to the table.

If

I would probably use a VBA routine to
-- Delete all records the work table (I would already have built the needed table)
-- Get a list of the branches
-- Build a loop and run an append query based on one branch at a time

Use the work table for any report(s). And if you run the query for one branch
at time, the speed should be faster and won't require the ranking at all.

INSERT INTO [WorkTable] (Branch, AcNo, LNamt)
SELECT Top 20 Branch, AcNo, LNamt
FROM [YourTable]
WHERE Branch = "00100" <<< a variable containing the specific branch
ORDER BY LNamt Desc

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

Hi John,

I've indexed all the fields and when i run it on 2-3 branches i can get the
result not more than a minute....But when i open the query to all branches i
will take more than an hour . My total records is around 350,000 and i have
14 branches. Each branch will have an average of 25000 records. I'm running
the query on access database which reside on my pc. I'm using Access 2003.

Thanks

Applying indexes should not cause a problem. It should significantly speed up
the operation of the query.

I continued to experiment and was able to process a table of a million records
in about 17 minutes. A table of slightly over 400,000 records took roughly 2
minutes 15 seconds to process.

The time will vary depending on how many records how are involved and how many
records are in each group that needs to be processed.

So my advice is add indexes.

Also, I have not tested this over a network using an Access (Jet) database.
So that may also be having an significant effect on your performance.

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

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

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