Prev: How can I refresh the master form from a subform?
Next: Delete Query Help
From: CBender on 24 May 2010 09:12
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" <CBender(a)discussions.microsoft.com> wrote in message
> news:F0249B22-07DD-41AF-BD68-6DC457C66274(a)microsoft.com...
> > 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" <CBender(a)discussions.microsoft.com> wrote in message
> >> news:E5439602-6EE4-4574-9A3B-24BB4C32530D(a)microsoft.com...
> >> > 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" <CBender(a)discussions.microsoft.com> wrote in message
> >> >> news:BB5F7C0D-C144-4D49-B918-71008311B7D7(a)microsoft.com...
> >> >> > 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)))))
> >> >> >>
> >> >> >>
First  |  Prev  | 
Pages: 1 2 3
Prev: How can I refresh the master form from a subform?
Next: Delete Query Help