A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Ranking based on Multiple Data Fields



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2010, 05:03 PM posted to microsoft.public.access.queries
CBender
external usenet poster
 
Posts: 23
Default Ranking based on Multiple Data Fields

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
  #2  
Old May 11th, 2010, 05:23 PM posted to microsoft.public.access.queries
CBender
external usenet poster
 
Posts: 23
Default Ranking based on Multiple Data Fields

Sorry, After reading my post I feel I need to clarify a couple of things.

I need the ranking to sort on the [Type2] value, then by [Host DN] value,
then by the [All BO] value.

This should provide somewhat of a stepping effect for the reporting as shown
below:

[Rank] [Host DN] [All BO]
[Type2]
1 3,293,090.80 458
3
2 173,563.20 214
3
3 1,029,110.22 8
2
4 877,394.54 6
2
5 3,090.80 218
1
6 2,450.38 188
1
7 15,578.32 65
7 15,578.32 65
8 9,824.67 57
9 548.14 38

I know that where duplicate values are reported duplicate ranking will be
listed and this is what I want.

I hope this clarified what I am looking to try and do.



Thanks,

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

  #3  
Old May 11th, 2010, 05:38 PM posted to microsoft.public.access.queries
CBender
external usenet poster
 
Posts: 23
Default Ranking based on Multiple Data Fields

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

  #4  
Old May 11th, 2010, 05:41 PM posted to microsoft.public.access.queries
CBender
external usenet poster
 
Posts: 23
Default Ranking based on Multiple Data Fields

[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

  #5  
Old May 11th, 2010, 05:43 PM posted to microsoft.public.access.queries
CBender
external usenet poster
 
Posts: 23
Default Ranking based on Multiple Data Fields

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

  #6  
Old May 11th, 2010, 07:03 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default Ranking based on Multiple Data Fields

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


  #7  
Old May 13th, 2010, 10:02 PM posted to microsoft.public.access.queries
CBender
external usenet poster
 
Posts: 23
Default Ranking based on Multiple Data Fields

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


  #8  
Old May 13th, 2010, 10:13 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default Ranking based on Multiple Data Fields

There is a way, indeed, to get the dense rank, but again, the easiest one is
to use a sequence of query (which are called automatically, so there is no
visible complexity added at the user level).

A first query will be to remove the dups: call that query QNoDup, for
example.
Next, rank QNoDup records. Since there is no dup, you will get 1, 2, 3, 4,
5, ... for ranks. That makes QRank.
Finally, join the original data with the second query, getting the rank from
that second query, QRank. The dups are re-introduced, but the ranks have
already been computed, correctly, to produce the dense rank, so it is a job
done.



Vanderghast, Access MVP



"CBender" wrote in message
...
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



  #9  
Old May 20th, 2010, 05:21 PM posted to microsoft.public.access.queries
CBender
external usenet poster
 
Posts: 23
Default Ranking based on Multiple Data Fields

Vanderghast,

I appreciate ALL of your HELP and SUPPORT. Using a slightly modified
version of your suggested coding I greated my query (please see below). My
query correctly ranks my data from "1" to "whatever" based on the [Ext B/O]
field.

In my last meeting to present the data I was told an, "Oh yeah, by the way..."
Now they want me to include the [All B/O] field in the ranking as well. So,
the ranking should look at the unique record for [Part Number], the [Ext
B/O], AND the [All B/O] fields when assigning the ranking designations to the
data.

While [Part Number] indicates a UNIQUE record, there are occasionally
duplicate values for BOTH the [Ext B/O] and the [All B/O] field data for the
record.

If possible, I would LIKE to get rankings for 1, 2, 3, 4, 4, 4, 5, 6, 7, 7,
8, etc...

The records should be sorted:
Ascending by [Rank]
Descending by [Ext B/O]
Descending by [All B/O]
Ascending by [Part Number]

Here is a visual example of the ranking requested:

Rank Ext B/O All B/O Part Number
1 $18,498.10 92 4682840
2 $15,321.75 5 4563896
3 $13,836.60 12 3832851
4 $10,377.41 1285 1684846
4 $10,377.41 1285 3821173
5 $8,936.55 571 9274022
6 $5,215.58 63 4503211
7 $0.00 10 1
8 $0.00 9 2
9 $0.00 8 3

Here is the query I am currently using. Can you please let me know what
changes I need to make to get the results based on my new reporting
requirements?


SELECT
(SELECT Count([VT].[Ext B/O])

FROM [tbl_Chart5a_Report4] AS VT

WHERE VT.[Ext B/O] tbl_Chart5a_Report4.[Ext B/O])+1 AS Rank,
tbl_Chart5a_Report4.[Part Number],
tbl_Chart5a_Report4.[Host DN],
tbl_Chart5a_Report4.[Ext B/O],
tbl_Chart5a_Report4.[All B/O]

FROM
tbl_Chart5a_Report4

GROUP BY
tbl_Chart5a_Report4.[Part Number],
tbl_Chart5a_Report4.[Host DN],
tbl_Chart5a_Report4.[Ext B/O],
tbl_Chart5a_Report4.[All B/O]

ORDER BY
tbl_Chart5a_Report4.[Ext B/O] DESC ,
tbl_Chart5a_Report4.[All B/O] DESC;



--
Chip


"vanderghast" wrote:

There is a way, indeed, to get the dense rank, but again, the easiest one is
to use a sequence of query (which are called automatically, so there is no
visible complexity added at the user level).

A first query will be to remove the dups: call that query QNoDup, for
example.
Next, rank QNoDup records. Since there is no dup, you will get 1, 2, 3, 4,
5, ... for ranks. That makes QRank.
Finally, join the original data with the second query, getting the rank from
that second query, QRank. The dups are re-introduced, but the ranks have
already been computed, correctly, to produce the dense rank, so it is a job
done.



Vanderghast, Access MVP



"CBender" wrote in message
...
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


  #10  
Old May 20th, 2010, 09:47 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default Ranking based on Multiple Data Fields

If you need to get a dense rank, your query where it is computed should
operate only on distinct data.


SELECT
(SELECT Count([VT].[Ext B/O])

FROM [tbl_Chart5a_Report4] AS VT

WHERE VT.[Ext B/O] tbl_Chart5a_Report4.[Ext B/O])+1 AS Rank,
tbl_Chart5a_Report4.[Part Number],
tbl_Chart5a_Report4.[Host DN],
tbl_Chart5a_Report4.[Ext B/O],
tbl_Chart5a_Report4.[All B/O]

FROM
tbl_Chart5a_Report4

GROUP BY
tbl_Chart5a_Report4.[Part Number],
tbl_Chart5a_Report4.[Host DN],
tbl_Chart5a_Report4.[Ext B/O],
tbl_Chart5a_Report4.[All B/O]

ORDER BY
tbl_Chart5a_Report4.[Ext B/O] DESC ,
tbl_Chart5a_Report4.[All B/O] DESC;



you should try:


SELECT
(SELECT Count([VT].[Ext B/O])

FROM [queryWithDisttinctDataFromTbl_Chart5a_Report4] AS VT

WHERE VT.[Ext B/O] tbl_Chart5a_Report4.[Ext B/O]
OR ( VT.[Ext B/O] = tbl_Chart5a_Report4.[Ext B/O]
AND ( VT.[All B/O] = tbl_Chart5a_Report4.[All B/O]
) )
) +1 AS Rank,
tbl_Chart5a_Report4.[Part Number],
tbl_Chart5a_Report4.[Host DN],
tbl_Chart5a_Report4.[Ext B/O],
tbl_Chart5a_Report4.[All B/O]

FROM
tbl_Chart5a_Report4

GROUP BY
tbl_Chart5a_Report4.[Part Number],
tbl_Chart5a_Report4.[Host DN],
tbl_Chart5a_Report4.[Ext B/O],
tbl_Chart5a_Report4.[All B/O]

ORDER BY
tbl_Chart5a_Report4.[Ext B/O] DESC ,
tbl_Chart5a_Report4.[All B/O] DESC;



where queryWithDisttinctDataFromTbl_Chart5a_Report4 would be a saved query
returning the data without duplicated tuples (for the selected fields taken
together):

SELECT DISTINCT [Ext B/O], [All B/O]
FROM tbl_Chart5a_Report4



Vanderghast, Access MVP


"CBender" wrote in message
...
Vanderghast,

I appreciate ALL of your HELP and SUPPORT. Using a slightly modified
version of your suggested coding I greated my query (please see below).
My
query correctly ranks my data from "1" to "whatever" based on the [Ext
B/O]
field.

In my last meeting to present the data I was told an, "Oh yeah, by the
way..."
Now they want me to include the [All B/O] field in the ranking as well.
So,
the ranking should look at the unique record for [Part Number], the [Ext
B/O], AND the [All B/O] fields when assigning the ranking designations to
the
data.

While [Part Number] indicates a UNIQUE record, there are occasionally
duplicate values for BOTH the [Ext B/O] and the [All B/O] field data for
the
record.

If possible, I would LIKE to get rankings for 1, 2, 3, 4, 4, 4, 5, 6, 7,
7,
8, etc...

The records should be sorted:
Ascending by [Rank]
Descending by [Ext B/O]
Descending by [All B/O]
Ascending by [Part Number]

Here is a visual example of the ranking requested:

Rank Ext B/O All B/O Part Number
1 $18,498.10 92 4682840
2 $15,321.75 5 4563896
3 $13,836.60 12 3832851
4 $10,377.41 1285 1684846
4 $10,377.41 1285 3821173
5 $8,936.55 571 9274022
6 $5,215.58 63 4503211
7 $0.00 10 1
8 $0.00 9 2
9 $0.00 8 3

Here is the query I am currently using. Can you please let me know what
changes I need to make to get the results based on my new reporting
requirements?


SELECT
(SELECT Count([VT].[Ext B/O])

FROM [tbl_Chart5a_Report4] AS VT

WHERE VT.[Ext B/O] tbl_Chart5a_Report4.[Ext B/O])+1 AS Rank,
tbl_Chart5a_Report4.[Part Number],
tbl_Chart5a_Report4.[Host DN],
tbl_Chart5a_Report4.[Ext B/O],
tbl_Chart5a_Report4.[All B/O]

FROM
tbl_Chart5a_Report4

GROUP BY
tbl_Chart5a_Report4.[Part Number],
tbl_Chart5a_Report4.[Host DN],
tbl_Chart5a_Report4.[Ext B/O],
tbl_Chart5a_Report4.[All B/O]

ORDER BY
tbl_Chart5a_Report4.[Ext B/O] DESC ,
tbl_Chart5a_Report4.[All B/O] DESC;



--
Chip


"vanderghast" wrote:

There is a way, indeed, to get the dense rank, but again, the easiest one
is
to use a sequence of query (which are called automatically, so there is
no
visible complexity added at the user level).

A first query will be to remove the dups: call that query QNoDup, for
example.
Next, rank QNoDup records. Since there is no dup, you will get 1, 2, 3,
4,
5, ... for ranks. That makes QRank.
Finally, join the original data with the second query, getting the rank
from
that second query, QRank. The dups are re-introduced, but the ranks have
already been computed, correctly, to produce the dense rank, so it is a
job
done.



Vanderghast, Access MVP



"CBender" wrote in message
...
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:55 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.