Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

maximum wins only

Status
Not open for further replies.

aspnet98

MIS
May 19, 2005
165
US
I have a recordset displaying the following that i am using in crystal:

ID Week Wins Difference name
1 1 1 2 jason
1 1 1 5 john
1 1 0 24 jay

I am grouping on the id and then the week. The report looks perfect for what i want to do, except that i only want to show the winner (in this case it is jason).

How can i do this?

Sometimes their may be a tie.

ID Week Wins Difference name
1 1 1 2 jason
1 1 1 2 john

and i need to show the two who tied.

is this possible?

cr 8.5
sql server 2000
 
my approach was this:
set two fomulas in the details section
@max maximum(wins,week)
@min minimum(dif,week)

in the details section, suppress formula:
{Wins} < {@max} and
{diff} > {@min}

but this fails.........is their a better approach?
 
What denotes that jason is the winner in your example ?



Gary Parker
MIS Data Analyst
Manchester, England
 
Is "wins" a summary or a database field?

-LB
 
wins is determined by most wins lowest difference

lb: wins is a database field
 
You need to do a selection within a selection, so I think the following approach would work. You could create a SQL expression {%maxwins} to return the highest number of wins:

(Select max(A.`wins`) from Table A where
A.`ID` = Table.`ID` and
A.`Week` = Table.`Week`)

Then you would use a record selection formula like:

{table.wins} = {%maxwins}

This should return all records with the maximum number of wins. Then to select the one or more records with the minimum {table.diff}, go to report->edit selection formula->GROUP and enter:

{table.diff} = minimum({table.diff},{table.week})

I'm assuming that "week" is actually a database field called {table.week}. If it is a date that you are grouping on weekly, you would have to adjust the expression and formula.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top