Top Values in Report

[Guest]

I tried to follow the instructions below, but I must be doing something
wrong. I have a bill number, amount, and CPT Code. I grouped by Bill Number.
I only want to see the 2 CPT Codes with the highest amounts.

I made a subreport based on a top 2 values. When the subreport runs by
itself, it shows only 2 CPT values and amounts. My understanding was that if
I placed this subreport in the Main report Billing footer and linked the bill
number fields, then it would show the top 2 values for each bill number. But
that didn't work. It shows only the top 2 values for the bills that match the
subreport when it's run by itself. Every other bill is blank.

Below is what I tried to follow from another post. I may not understand what
it is saying. What am I doing wrong.

Hi Ed, That sounds like fun. Any way, the way to approach this is to create
a report for the Members, grouped by member with a footer per member, than in
the memeber footer insert a subreport, I would probably create a generic
subreport based on the Rounds table linked by the meber number.
Now open the subreport in design view and modify the source query order by
score descending and to show only the top two
records.
This should do it.
Fons

 

[Duane Hookom]

I think the advice might be wrong and I should have tested this when I read
the post earlier.

I would add a text box to the detail section of the subreport:
Name: txtCount
Control Source: =1
Running Sum: Over All
Visible: No
Then add code to the On Format event of the detail section of this
subreport:
Cancel = Me.txtCount >2

This should cancel the printing of more than 2 records per instance of the
subreport.

 

[Guest]

Thanks! I got it to work! But, now I need to add those two values that are
showing to get a subtotal for the amounts showing, along with a grand total
at the end of the report. Do I give the fields in the report a name and refer
to those fields on my main report to add them up?

 

[Guest]

I need to add something to this. Not only do I need the amounts to add, I
need the second amount to display only 50% of its actual amount. So, Amount 1
displays its acutal value, but the 2nd Amount displays half its actual value.
Any other values don't display at all (which is working fine, thanks to you!)

 

[Guest]

I tried putting a formula in the amount field,
=IIf([txtcount]=2,[amount]*0.5,[amount])
But this didn't work. I received an error message.

 

[Duane Hookom]

Make sure the name of the text box is not the name of a field/column.
You may need to use a Running Sum to accumulate the values of a calculated
text box.

--
Duane Hookom
MS Access MVP
--

I tried putting a formula in the amount field,
=IIf([txtcount]=2,[amount]*0.5,[amount])
But this didn't work. I received an error message.

I need to add something to this. Not only do I need the amounts to add, I
need the second amount to display only 50% of its actual amount. So,
Amount 1
displays its acutal value, but the 2nd Amount displays half its actual
value.
Any other values don't display at all (which is working fine, thanks to
you!)