can the report refilter of only the top 20 of each group in the field?

Y

ysop

How ?
To make the report display only the top 20 of each of all
categories in that value field. And, Yes at the footer
I 'd like to have the sum of value of not the top 20 but
to all items in those categories.
 
D

Duane Hookom

You can add a text box in the detail section:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Visible: No
Then add code to the detail section On Format event
Cancel = Me.txtCount >20
 
T

trsss

So , it's success only first part.
But on the Detail Section on Format to put
cancel = me.txtcount>20
has referred to the error of no "cancel" function.
and object "Me" not found.
 
D

Duane Hookom

Did you place the line I provided in the code behind the report? I should
have been more clear that this can't be placed in the event property.
Your code should look similar to:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = Me.txtCount >20
End Sub
 
G

Guest

Thanks it's work now.
But another thing is I'd like to do is the sum of the
value of only 20 items in each category together with the
sum of all in the footer. I'd like to have both of them to
show.
 
D

Duane Hookom

What have you tried. I wouldn't know exactly how to do this without
re-creating your report and playing with it for a while to see what happens.
I don't feel right about taking time to do this without you first seeing how
far you get.

If you can't find a solution, come back with what you tried and your
results.
 
R

rdos

Thanks for your advice

But the result of the sum of the value of each category
for after the refilter on only 20 items not shown for. So
the asking to making the sum of the value at the footer
shown to sum total of all items, but it's fine at the
first place. In doing so, the compare result of the top 20
and the all items is needed to do here at the footer. So
if I was to begin making the new report the result was the
same as to shown only the sum value of all items not with
the value of sum of only 20 items.
 
D

Duane Hookom

I'm sorry, you have lost me. Are you the original poster? Can you be more
clear?
 
D

Duane Hookom

And you have tried what? You may need to do some SQL modifications. If you
need further assistance, you should share your table(s) structure.
 
J

jpb

hoo::
I have tried so much but the only change was made is only
on the format. It's okey. If you could do some more, I put
the txtcount on the footer and want to show the last value
as the detail section only the last sum(not sum all).
 
D

Duane Hookom

To total only the visible values assuming you want to sum a field named
[Qty].
- you have a text box [txtCount] that numbers the detail section
- Add a text box in the detail section
Name: txtRunSumQty
Control Source: =Abs([txtCount]<=2)*[Quantity]
Running Sum: Over Group
Visible: No
-Add a text box to the Group Footer section
Control Source: =txtRunSumQty
 
Top