Limit Number Of Rows In Report

[zyus]

How to limit number of rows in report group by branchcode.

In my report, i grouped by branchcode (header) and netbalance (desc). I want
to limit the row up to 20, showing account with highest netbalance.

Thanks

 

[Wayne-I-M]

Hi

You can write some code do this for you or you can use a wizard.

So to use the wizard
Base your report on a query
Get this working showing all recods with the layout and other designs that
you're happy with
Then open the query in design view
In the top section (where the tables are) right click and view the properties
In the "Top" row just select the number or percentage that you want - in
this case just type in 20.

 

[zyus]

I have designed the report but each branchcode (13 branches) will give me
more than 20 rows. I intend to limit up to 20 rows for each branchcode with
netbal(desc) and not to limit overall rows as 20. If i put 20 in query Top
Values it will produce only 20 rows for all branches.

 

[Wayne-I-M]

No worries - I take it you each branch as a subreport - if this is the case
just use a subquery.

If this is not the case post back with more info on how you report is laid
out and what it is based on

 

[John Spencer MVP]

Post the SQL statement of the query you are using in the report and you can
use that to limit the return to the top 20 per branchcode.

Another way is to use VBA to hide the rows in the report that you don't want
to print.

Add a new control to the detail section of your report
Name it TxtLineCount
Set its source to =1
Set its running total to Over Group

In the Format event of the detail add VBA code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Section(acDetail).Visible = (Me.txtLineCount <= 20)
End Sub

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

 

[zyus]

My sql statement for query
SELECT [Tbl-SKS].LNAME, [Tbl-SKS].IDNO, [Tbl-SKS].BRNCD, [Tbl-SKS].MODCD,
[Tbl-SKS].CRLINE, [Tbl-SKS].FCCD, [Tbl-SKS].PRLINE, [Tbl-SKS].PRTYP,
[Tbl-SKS].ACNO, [Tbl-SKS].LNAMT, [Tbl-SKS].GRSBAL, [Tbl-SKS].NET_BAL,
[Tbl-SKS].COLMTH, [Tbl-SKS].ARRAMT, [Tbl-SKS].ARRMTH, [Tbl-SKS].NPLFLG,
[Tbl-SKS].NETIIS, [Tbl-SKS].NETPRO, [Tbl-SKS].CATCD, [Tbl-SKS].CATDESC,
[Tbl-SKS].LSTCOLDT, [Tbl-SKS].FSTRLSEDT, [Tbl-SKS].APRDT,
[Tbl-SKS].MARKOFFICER, [Tbl-SKS].UNDRAWN, [Tbl-SKS].PENAMT
FROM [Tbl-SKS]
WHERE ((([Tbl-SKS].CATCD)="HL"))
ORDER BY [Tbl-SKS].NET_BAL DESC;

I tried to use vba as suggested but i got this error "compile error-method
or data member not found" and item .txtLineCount was highlighted.

 

[zyus]

My sql statement for query
SELECT [Tbl-SKS].LNAME, [Tbl-SKS].IDNO, [Tbl-SKS].BRNCD, [Tbl-SKS].MODCD,
[Tbl-SKS].CRLINE, [Tbl-SKS].FCCD, [Tbl-SKS].PRLINE, [Tbl-SKS].PRTYP,
[Tbl-SKS].ACNO, [Tbl-SKS].LNAMT, [Tbl-SKS].GRSBAL, [Tbl-SKS].NET_BAL,
[Tbl-SKS].COLMTH, [Tbl-SKS].ARRAMT, [Tbl-SKS].ARRMTH, [Tbl-SKS].NPLFLG,
[Tbl-SKS].NETIIS, [Tbl-SKS].NETPRO, [Tbl-SKS].CATCD, [Tbl-SKS].CATDESC,
[Tbl-SKS].LSTCOLDT, [Tbl-SKS].FSTRLSEDT, [Tbl-SKS].APRDT,
[Tbl-SKS].MARKOFFICER, [Tbl-SKS].UNDRAWN, [Tbl-SKS].PENAMT
FROM [Tbl-SKS]
WHERE ((([Tbl-SKS].CATCD)="HL"))
ORDER BY [Tbl-SKS].NET_BAL DESC;

In my report i group based on BRNCD with Header and try to limit up to 20
records for each BRNCD

 

[John Spencer MVP]

That VBA should have worked. Did you add a control to the section and name it
as suggested?

The following should work to give you the TOP 20 NET_Bal for each BRNCD. I am
assuming that BRNCD will give you the branch.

SELECT [Tbl-SKS].LNAME
, [Tbl-SKS].IDNO
, [Tbl-SKS].BRNCD
, [Tbl-SKS].MODCD
, [Tbl-SKS].CRLINE
, [Tbl-SKS].FCCD
, [Tbl-SKS].PRLINE
, [Tbl-SKS].PRTYP
, [Tbl-SKS].ACNO
, [Tbl-SKS].LNAMT
, [Tbl-SKS].GRSBAL
, [Tbl-SKS].NET_BAL,
[Tbl-SKS].COLMTH, [Tbl-SKS].ARRAMT, [Tbl-SKS].ARRMTH, [Tbl-SKS].NPLFLG,
[Tbl-SKS].NETIIS, [Tbl-SKS].NETPRO, [Tbl-SKS].CATCD, [Tbl-SKS].CATDESC,
[Tbl-SKS].LSTCOLDT, [Tbl-SKS].FSTRLSEDT, [Tbl-SKS].APRDT,
[Tbl-SKS].MARKOFFICER, [Tbl-SKS].UNDRAWN, [Tbl-SKS].PENAMT
FROM [Tbl-SKS]
WHERE [Tbl-SKS].CATCD="HL"
AND NET_BAL IN
(Select TOP 20 Temp.Net_BAL
FROM [Tbl-SKS] as Temp
WHERE Temp.BRNCD = [Tbl-SKS].BRNCD
ORDER BY Net_BAL DESC)


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

My sql statement for query
SELECT [Tbl-SKS].LNAME, [Tbl-SKS].IDNO, [Tbl-SKS].BRNCD, [Tbl-SKS].MODCD,
[Tbl-SKS].CRLINE, [Tbl-SKS].FCCD, [Tbl-SKS].PRLINE, [Tbl-SKS].PRTYP,
[Tbl-SKS].ACNO, [Tbl-SKS].LNAMT, [Tbl-SKS].GRSBAL, [Tbl-SKS].NET_BAL,
[Tbl-SKS].COLMTH, [Tbl-SKS].ARRAMT, [Tbl-SKS].ARRMTH, [Tbl-SKS].NPLFLG,
[Tbl-SKS].NETIIS, [Tbl-SKS].NETPRO, [Tbl-SKS].CATCD, [Tbl-SKS].CATDESC,
[Tbl-SKS].LSTCOLDT, [Tbl-SKS].FSTRLSEDT, [Tbl-SKS].APRDT,
[Tbl-SKS].MARKOFFICER, [Tbl-SKS].UNDRAWN, [Tbl-SKS].PENAMT
FROM [Tbl-SKS]
WHERE ((([Tbl-SKS].CATCD)="HL"))
ORDER BY [Tbl-SKS].NET_BAL DESC;

I tried to use vba as suggested but i got this error "compile error-method
or data member not found" and item .txtLineCount was highlighted.

Post the SQL statement of the query you are using in the report and you can
use that to limit the return to the top 20 per branchcode.

Another way is to use VBA to hide the rows in the report that you don't want
to print.

Add a new control to the detail section of your report
Name it TxtLineCount
Set its source to =1
Set its running total to Over Group

In the Format event of the detail add VBA code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Section(acDetail).Visible = (Me.txtLineCount <= 20)
End Sub

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

 

[zyus]

The VBA worked when i add new control. Got some error when i used old control
(before i add the codes).

Thanks for your help.

p/s For the sql query...there's some performance issue maybe due to my
record (350,000 records, and 13 BRNCD). It took sometime (>10mins) to produce
the output)

That VBA should have worked. Did you add a control to the section and name it
as suggested?

The following should work to give you the TOP 20 NET_Bal for each BRNCD. I am
assuming that BRNCD will give you the branch.

SELECT [Tbl-SKS].LNAME
, [Tbl-SKS].IDNO
, [Tbl-SKS].BRNCD
, [Tbl-SKS].MODCD
, [Tbl-SKS].CRLINE
, [Tbl-SKS].FCCD
, [Tbl-SKS].PRLINE
, [Tbl-SKS].PRTYP
, [Tbl-SKS].ACNO
, [Tbl-SKS].LNAMT
, [Tbl-SKS].GRSBAL
, [Tbl-SKS].NET_BAL,
[Tbl-SKS].COLMTH, [Tbl-SKS].ARRAMT, [Tbl-SKS].ARRMTH, [Tbl-SKS].NPLFLG,
[Tbl-SKS].NETIIS, [Tbl-SKS].NETPRO, [Tbl-SKS].CATCD, [Tbl-SKS].CATDESC,
[Tbl-SKS].LSTCOLDT, [Tbl-SKS].FSTRLSEDT, [Tbl-SKS].APRDT,
[Tbl-SKS].MARKOFFICER, [Tbl-SKS].UNDRAWN, [Tbl-SKS].PENAMT
FROM [Tbl-SKS]
WHERE [Tbl-SKS].CATCD="HL"
AND NET_BAL IN
(Select TOP 20 Temp.Net_BAL
FROM [Tbl-SKS] as Temp
WHERE Temp.BRNCD = [Tbl-SKS].BRNCD
ORDER BY Net_BAL DESC)


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

My sql statement for query
SELECT [Tbl-SKS].LNAME, [Tbl-SKS].IDNO, [Tbl-SKS].BRNCD, [Tbl-SKS].MODCD,
[Tbl-SKS].CRLINE, [Tbl-SKS].FCCD, [Tbl-SKS].PRLINE, [Tbl-SKS].PRTYP,
[Tbl-SKS].ACNO, [Tbl-SKS].LNAMT, [Tbl-SKS].GRSBAL, [Tbl-SKS].NET_BAL,
[Tbl-SKS].COLMTH, [Tbl-SKS].ARRAMT, [Tbl-SKS].ARRMTH, [Tbl-SKS].NPLFLG,
[Tbl-SKS].NETIIS, [Tbl-SKS].NETPRO, [Tbl-SKS].CATCD, [Tbl-SKS].CATDESC,
[Tbl-SKS].LSTCOLDT, [Tbl-SKS].FSTRLSEDT, [Tbl-SKS].APRDT,
[Tbl-SKS].MARKOFFICER, [Tbl-SKS].UNDRAWN, [Tbl-SKS].PENAMT
FROM [Tbl-SKS]
WHERE ((([Tbl-SKS].CATCD)="HL"))
ORDER BY [Tbl-SKS].NET_BAL DESC;

I tried to use vba as suggested but i got this error "compile error-method
or data member not found" and item .txtLineCount was highlighted.

Post the SQL statement of the query you are using in the report and you can
use that to limit the return to the top 20 per branchcode.

Another way is to use VBA to hide the rows in the report that you don't want
to print.

Add a new control to the detail section of your report
Name it TxtLineCount
Set its source to =1
Set its running total to Over Group

In the Format event of the detail add VBA code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Section(acDetail).Visible = (Me.txtLineCount <= 20)
End Sub

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

zyus wrote:
How to limit number of rows in report group by branchcode.

In my report, i grouped by branchcode (header) and netbalance (desc). I want
to limit the row up to 20, showing account with highest netbalance.

Thanks

 

[John Spencer MVP]

Yes, a correlated subquery will be slow. It can be faster IF you have the
right fields indexed. In your particular case that means that BrnCD must be
indexed and Net_Bal must be indexed. If they are not and you do index them
the performance improvement is often significant.

There are other ways to improve the performance and other ways to get the TOP
20. This one is the simplest solution.

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

 

[zyus]

Hi John,

Just to add on below solution. I manage to limit by 20 rows or record. How
to calculate the sub total for the 20 record by brncd. When i sum by brncd i
got the total sum of brncd but not the top 20.

Thanks

That VBA should have worked. Did you add a control to the section and name it
as suggested?

The following should work to give you the TOP 20 NET_Bal for each BRNCD. I am
assuming that BRNCD will give you the branch.

SELECT [Tbl-SKS].LNAME
, [Tbl-SKS].IDNO
, [Tbl-SKS].BRNCD
, [Tbl-SKS].MODCD
, [Tbl-SKS].CRLINE
, [Tbl-SKS].FCCD
, [Tbl-SKS].PRLINE
, [Tbl-SKS].PRTYP
, [Tbl-SKS].ACNO
, [Tbl-SKS].LNAMT
, [Tbl-SKS].GRSBAL
, [Tbl-SKS].NET_BAL,
[Tbl-SKS].COLMTH, [Tbl-SKS].ARRAMT, [Tbl-SKS].ARRMTH, [Tbl-SKS].NPLFLG,
[Tbl-SKS].NETIIS, [Tbl-SKS].NETPRO, [Tbl-SKS].CATCD, [Tbl-SKS].CATDESC,
[Tbl-SKS].LSTCOLDT, [Tbl-SKS].FSTRLSEDT, [Tbl-SKS].APRDT,
[Tbl-SKS].MARKOFFICER, [Tbl-SKS].UNDRAWN, [Tbl-SKS].PENAMT
FROM [Tbl-SKS]
WHERE [Tbl-SKS].CATCD="HL"
AND NET_BAL IN
(Select TOP 20 Temp.Net_BAL
FROM [Tbl-SKS] as Temp
WHERE Temp.BRNCD = [Tbl-SKS].BRNCD
ORDER BY Net_BAL DESC)


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

My sql statement for query
SELECT [Tbl-SKS].LNAME, [Tbl-SKS].IDNO, [Tbl-SKS].BRNCD, [Tbl-SKS].MODCD,
[Tbl-SKS].CRLINE, [Tbl-SKS].FCCD, [Tbl-SKS].PRLINE, [Tbl-SKS].PRTYP,
[Tbl-SKS].ACNO, [Tbl-SKS].LNAMT, [Tbl-SKS].GRSBAL, [Tbl-SKS].NET_BAL,
[Tbl-SKS].COLMTH, [Tbl-SKS].ARRAMT, [Tbl-SKS].ARRMTH, [Tbl-SKS].NPLFLG,
[Tbl-SKS].NETIIS, [Tbl-SKS].NETPRO, [Tbl-SKS].CATCD, [Tbl-SKS].CATDESC,
[Tbl-SKS].LSTCOLDT, [Tbl-SKS].FSTRLSEDT, [Tbl-SKS].APRDT,
[Tbl-SKS].MARKOFFICER, [Tbl-SKS].UNDRAWN, [Tbl-SKS].PENAMT
FROM [Tbl-SKS]
WHERE ((([Tbl-SKS].CATCD)="HL"))
ORDER BY [Tbl-SKS].NET_BAL DESC;

I tried to use vba as suggested but i got this error "compile error-method
or data member not found" and item .txtLineCount was highlighted.

Post the SQL statement of the query you are using in the report and you can
use that to limit the return to the top 20 per branchcode.

Another way is to use VBA to hide the rows in the report that you don't want
to print.

Add a new control to the detail section of your report
Name it TxtLineCount
Set its source to =1
Set its running total to Over Group

In the Format event of the detail add VBA code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Section(acDetail).Visible = (Me.txtLineCount <= 20)
End Sub

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

zyus wrote:
How to limit number of rows in report group by branchcode.

In my report, i grouped by branchcode (header) and netbalance (desc). I want
to limit the row up to 20, showing account with highest netbalance.

Thanks