After reading my post I thought it best if I tried to clarify a few things.

If [Host DN] = 20000 AND [All BO] = 100 THEN [Type] = 3
If [Host DN] = 20000 THEN [Type] = 2
If [All BO] = 100 THEN Type = 1


The data needs to be Ranked and sorted as shown in the example below:


[Rank] [Host DN] [All BO]
[Type]
1 328,155.34 831
3
2 134,728.16 416
3
3 1,415,578.32 87
2
4 987,821.27 69
2
5 15,578.32 1,566
1
6 1,878.81 865
1
7 543.02 72
8 89.19 43







--
Chip


"CBender" wrote:

I am trying to Rank a field based on multiple sort criteria. My query sorts
correctly, but the Ranking assignments are not correct. Could someone please
review my SQL query and let me know how to correct this problem?

I need to sort Descending by [Type2] AS WELL AS Descending by [Ext B/O]



I used the following to assign my [Type2] field data:

Type2: IIf([All BO]=100 And [Host DN]=20000,3,(IIf([Host
DN]=20000,2,(IIf([All BO]=100,1)))))


I am currently using the following to create my Ranking:

Rank: (SELECT Count([VT].[Ext BO]) FROM [tmp_tbl_Chart5a_1] AS VT WHERE
VT.[Ext BO] tmp_tbl_Chart5a_1.[Ext BO])+1


I know the Ranking I used sorts ONLY on the [Ext BO] field but I am not a
coder and do not know how to modify the format of my query to get what I need.


Can someone please help??


Thanks,
--
Chip