Ranking with Nulls

[jas580]

I have a query that returns the following Ratio and Rank. It's been working
perfectly until null values came along. I need records with null ratios
included, but I need their ranking to be null. They are currently getting
the highest rating.

Company Ratio Ratio Rank Desired Rank
CompA 5.5 0 0
CompB "null" 0 "null"
CompC 4.2 1 1
CompD 3.1 2 2
CompE 2.8 3 3

Below is the code I've been using for Ratio Rank. Can someone tell me what
to add so that a null Ratio gets a null Ranking? Thanks!

Ratio Rank: (Select Count(*) from [Ratios-BestWorst] Where
[Ratios-BestWorst].[Ratio] > [Ratios-BestWorst1].[Ratio] And
[Ratios-BestWorst].[Sector Name]=[Ratios-BestWorst1].[Sector Name] And
[Ratios-BestWorst].[DateCount]=[Ratios-BestWorst1].[DateCount])

-jeff

 

[KARL DEWEY]

Try this ---
Ratio Rank: IIF([Ratio] Is Null, Null,(Select Count(*) from
[Ratios-BestWorst] Where [Ratios-BestWorst].[Ratio] >
[Ratios-BestWorst1].[Ratio] And
[Ratios-BestWorst].[Sector Name]=[Ratios-BestWorst1].[Sector Name] And
[Ratios-BestWorst].[DateCount]=[Ratios-BestWorst1].[DateCount]))

I would also change the greater than to >= in case any or equal.

 

[jas580]

Perfect! thank you, karl!

-jeff

Try this ---
Ratio Rank: IIF([Ratio] Is Null, Null,(Select Count(*) from
[Ratios-BestWorst] Where [Ratios-BestWorst].[Ratio] >
[Ratios-BestWorst1].[Ratio] And
[Ratios-BestWorst].[Sector Name]=[Ratios-BestWorst1].[Sector Name] And
[Ratios-BestWorst].[DateCount]=[Ratios-BestWorst1].[DateCount]))

I would also change the greater than to >= in case any or equal.
--
KARL DEWEY
Build a little - Test a little

I have a query that returns the following Ratio and Rank. It's been working
perfectly until null values came along. I need records with null ratios
included, but I need their ranking to be null. They are currently getting
the highest rating.

Company Ratio Ratio Rank Desired Rank
CompA 5.5 0 0
CompB "null" 0 "null"
CompC 4.2 1 1
CompD 3.1 2 2
CompE 2.8 3 3

Below is the code I've been using for Ratio Rank. Can someone tell me what
to add so that a null Ratio gets a null Ranking? Thanks!

Ratio Rank: (Select Count(*) from [Ratios-BestWorst] Where
[Ratios-BestWorst].[Ratio] > [Ratios-BestWorst1].[Ratio] And
[Ratios-BestWorst].[Sector Name]=[Ratios-BestWorst1].[Sector Name] And
[Ratios-BestWorst].[DateCount]=[Ratios-BestWorst1].[DateCount])

-jeff