Ranking with Nulls

J

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
 
K

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.
 
J

jas580

Perfect! thank you, karl!

-jeff

KARL DEWEY said:
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


jas580 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top