Top Values in Report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chris - 09 Feb 2005 21:11 GMT

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
"Ed" wrote:

> Thanks for trying to help. If your familar with Trap shooting, thats what
> I'm trying to set up database for league. Let me simple it down for me. I
> have a score keeping database.
> Member table, Rounds table, One to many. The rounds table can have as many
> records as the member shoots that week. I want the report to display only
> top 2 rounds and total for that week. so the detail section would display
> top 2 scores for week 1. then top 2 scores for week 2 etc... I have a field
> in each round for week number.

Reply to this Message

Duane Hookom - 09 Feb 2005 21:35 GMT

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.

Signature

Duane Hookom
MS Access MVP
--

>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
[quoted text clipped - 39 lines]
>> field
>> in each round for week number.

Reply to this Message

chris - 10 Feb 2005 13:57 GMT

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?

> I think the advice might be wrong and I should have tested this when I read
> the post earlier.
[quoted text clipped - 54 lines]
> >> field
> >> in each round for week number.

Reply to this Message

chris - 10 Feb 2005 14:15 GMT

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!)

> 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
[quoted text clipped - 59 lines]
> > >> field
> > >> in each round for week number.

Reply to this Message

chris - 10 Feb 2005 14:21 GMT

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
[quoted text clipped - 64 lines]
> > > >> field
> > > >> in each round for week number.

Reply to this Message

Duane Hookom - 10 Feb 2005 15:04 GMT

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.

Signature

Duane Hookom
MS Access MVP
--

>I tried putting a formula in the amount field,
> =IIf([txtcount]=2,[amount]*0.5,[amount])
[quoted text clipped - 94 lines]
>> > > >> field
>> > > >> in each round for week number.

Reply to this Message