Ranking based on Multiple Data Fields

Discussion in 'Microsoft Access Queries' started by CBender, May 11, 2010.

  1. CBender

    CBender Guest

    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
     
    CBender, May 11, 2010
    #1
    1. Advertisements

  2. CBender

    CBender Guest

    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
     
    CBender, May 11, 2010
    #2
    1. Advertisements

  3. CBender

    CBender Guest

    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
     
    CBender, May 11, 2010
    #3
  4. CBender

    CBender Guest

    [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
     
    CBender, May 11, 2010
    #4
  5. CBender

    CBender Guest

    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
     
    CBender, May 11, 2010
    #5
  6. CBender

    vanderghast Guest

    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
    news:...
    > 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
     
    vanderghast, May 11, 2010
    #6
  7. CBender

    CBender Guest

    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
    > news:...
    > > 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

    >
     
    CBender, May 13, 2010
    #7
  8. CBender

    vanderghast Guest

    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
    news:...
    > 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
    >> news:...
    >> > 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

    >>
     
    vanderghast, May 13, 2010
    #8
  9. CBender

    CBender Guest

    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
    > news:...
    > > 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
    > >> news:...
    > >> > 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
    > >>

    >
     
    CBender, May 20, 2010
    #9
  10. CBender

    vanderghast Guest

    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
    news:...
    > 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
    >> news:...
    >> > 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
    >> >> news:...
    >> >> > 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
    >> >>

    >>
     
    vanderghast, May 20, 2010
    #10
  11. CBender

    CBender Guest

    THIS IS EXACTLY WHAT I WAS LOOKING FOR!!!!!

    I REALLY appreciate your assistance!!!



    Thanks!!!
    --
    Chip


    "vanderghast" wrote:

    > 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
    > news:...
    > > 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
    > >> news:...
    > >> > 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
    > >> >> news:...
    > >> >> > 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)))))
    > >> >> >>
    > >> >> >>
     
    CBender, May 24, 2010
    #11
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    ranking a query based on unique symbol

    Guest, Feb 7, 2005, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    156
    Marshall Barton
    Feb 8, 2005
  2. Guest

    Ranking records in sorted list based on expression

    Guest, Mar 17, 2006, in forum: Microsoft Access Queries
    Replies:
    7
    Views:
    154
    Guest
    Mar 20, 2006
  3. Jeff Polack

    Ranking Query Based on Aggregate

    Jeff Polack, Nov 26, 2007, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    159
    Michael Gramelspacher
    Nov 26, 2007
  4. ChuckW

    Ranking Customers based on Total Sales

    ChuckW, Mar 10, 2008, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    182
    Allen Browne
    Mar 11, 2008
  5. wilkins

    Ranking (Look for previous ranking)

    wilkins, Jan 10, 2012, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    624
    Bob Barrows
    Jan 11, 2012
Loading...

Share This Page