Thanks for the support.

I do have duplicate values in the Ext BO field and I do get rankings that
are replicated like 1, 1, 1, 1, then I will get 5, 6, etc...

Is there a way to get these rankings to show as 1, 1, 1, 2, 3, 4, 4, 5, etc. ?


My SQL codeing is below:

SELECT (SELECT Count([VT].[Ext BO]) FROM [tbl_Chart5_Report4] AS VT WHERE
VT.[Ext BO] tbl_Chart5_Report4.[Ext BO])+1 AS Rank,
tbl_Chart5_Report4.[Part Number], tbl_Chart5_Report4.[Host DN],
tbl_Chart5_Report4.[Ext BO], tbl_Chart5_Report4.[All BO]
FROM tbl_Chart5_Report4
GROUP BY tbl_Chart5_Report4.[Part Number], tbl_Chart5_Report4.[Host DN],
tbl_Chart5_Report4.[Ext BO], tbl_Chart5_Report4.[All BO]
ORDER BY tbl_Chart5_Report4.[Ext BO] DESC , tbl_Chart5_Report4.[All BO] DESC;



Thanks for your help!!!

--
Chip


"vanderghast" wrote:

If there is no duplicated couple (type, [all bo]), then a join could be
faster :

SELECT a.[host dn], LAST(a.[all bo]), LAST(a.type), COUNT(*) as rank
FROM table AS a INNER JOIN table AS b
ON a.type b.type
OR (a.type = b.type AND a.[all bo] = b[all bo])
GROUP BY a.[host dn]

(I also assume Host DN are unique, no dup), else, we have to modify the
GROUP BY clause).


If there are dup, and if you want the low mark for equality (ie. ranks are
1, 1, 1, 4, 5 ... if there are 3 possible records for the first place,
all three get rank 1, and the fourth record get rank of 4, while there is no
second, no third, in that case), then you can use something like:

SELECT a.[host dn], a.[all bo], a.type,
(SELECT COUNT(*)
FROM table AS b
WHERE a.type b.type
OR (a.type = b.type AND a.[all bo] = b[all bo])) as rank
FROM table AS a



Haven't tested (typo or otherwise).



Vanderghast, Access MVP


"CBender" wrote in message
...
Hopefully THIS time the chart will post correctly!!!

[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