PC Review


Reply
Thread Tools Rate Thread

Ranking based on Multiple Data Fields

 
 
CBender
Guest
Posts: n/a
 
      24th May 2010
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:(E-Mail Removed)...
> >> >> > 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)))))
> >> >> >>
> >> >> >>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Ranking (Look for previous ranking) wilkins Microsoft Access Queries 3 11th Jan 2012 01:22 PM
multiple ranking (sorting), based on diff. criteria Eddy Stan Microsoft Excel Worksheet Functions 0 27th Oct 2009 09:56 PM
Ranking data based on data in other cells Pelham Microsoft Excel Discussion 3 2nd Sep 2009 02:37 AM
Ranking based on sum across multiple locations Ryan Microsoft Excel Worksheet Functions 1 28th Feb 2008 10:19 PM
Combobox based on expression and data entry based on multiple fields Fred Boer Microsoft Access Forms 11 25th Sep 2003 03:03 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:42 AM.