Looking for Help to correct my Ranking

[TBC]

I’m trying to correct my ranking formula. I was using A, but after running the report with all the other criteria I notices it was backwards. I tried B and C, and still couldn’t get the Ranking correct.

How would I change this from pulling

1-500
to
500-1

A

Rank: (Select count(*) from Rank_tbl_1 as B where Rank_tbl_1.TotalRecords > B.TotalRecords)

B

Rank: (Select count(*) from Rank_tbl_1 as B where Rank_tbl_1.TotalRecords < B.TotalRecords)

[/code]
C

[code]Rank: (Select count(*) from Rank_tbl_1 as B where B.TotalRecords > Rank_tbl_1.TotalRecords)

current report looks like
Rank
1
2
3
4
5
6
Would like
Rank
6
5
4
3
2
1
Thanks for your time and help
TCB

 

[boblarson]

Set the sort on Rank to Descending.

 

[boblarson]

Oh and if it is in a Report you have to set the sort order in the Report's Sorting And Grouping as it doesn't carry over sorts from queries most of the time.

 

[TBC]

is there a way I can have it corrected in my query? Thats kind of what I'm needing

 

[boblarson]

is there a way I can have it corrected in my query? Thats kind of what I'm needing

Now that I think about it, you probably would need to save the current query and then use that one in a separate query and then put a sort on the Rank field to DESCENDING. I don't think it would work in the first one because it is ranking them.

 

[vbaInet]

Or:

Rank: (Select count(*) from Rank_tbl_1 as A) - (Select count(*) from Rank_tbl_1 as B where Rank_tbl_1.TotalRecords < B.TotalRecords)

Where TotalRecords should be sorted in Ascending.

 

[TBC]

I really appreciate you giving it a try vbaInet, but it’s still backwards. anything else I can try?

 

[vbaInet]

If A gives you your ranking, use > instead of < in the code I gave,

 

[TBC]

Thats the first thing I tried, not sure why that didnt work? Do you have any other ideas I could try?

 

[vbaInet]

There's no way either one of those won't work. I've just tested what I wrote and it works so obviously you're not doing something right. Let's see your db.

 

[TBC]

you were right again vbaInet, Just needed to change the > to <

Thanks for the help

 

[vbaInet]

No problemo!