Re: Ranking Top 3 Results

Tech-Archive recommends: Fix windows errors by optimizing your registry

Didn't get chance last night to reply. Will be back in touch soon.

--

Ken Snell
<MS ACCESS MVP>

"Tina" <Tina@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8FA8790A-A7C5-4B28-B93A-A820B32E6DAC@xxxxxxxxxxxxxxxx
OprID OprName ID Deliv rank
ACN Air Canada 943 2/2/2010 1
ACN Air Canada 949 3/5/2010 1
ACN Air Canada 950 3/5/2010 1
BEJ Air China 824 6/4/2008 1
BEJ Air China 830 7/5/2008 1
BEJ Air China 868 2/1/2009 1


"Ken Snell (MVP)" wrote:

Show us some sample data / records so that we can see what values you're
trying to use.

--

Ken Snell
<MS ACCESS MVP>

"Tina" <Tina@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:053268DA-257C-4887-962A-9E0F3150DD48@xxxxxxxxxxxxxxxx
Deliv field is a date field.

"Ken Snell (MVP)" wrote:

What is the data type of Deliv field? What types of values are in it?

--

Ken Snell
<MS ACCESS MVP>

"Tina" <Tina@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FB2DC9BC-78DE-4434-9B08-F24FA73097F3@xxxxxxxxxxxxxxxx
My ranking produces "1" for every record. I'm trying to get it to
rank
1-2-3
for each record grouped by the operator.

"Ken Snell (MVP)" wrote:

Basic query looks ok. What is not working?

--

Ken Snell
<MS ACCESS MVP>



"Tina" <Tina@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:77C40B7D-FE2E-4144-BA22-B149F612579B@xxxxxxxxxxxxxxxx
I have the following query built in which I am trying to Rank a
very
simple
1st, 2nd, 3rd (1,2,3) for the records that the query has produced
but
for
some reason evry record is ranked 1. Can someone please review
and
let
me
know where I am going wrong, what adjustments do I need to make
in
order
to
produce three ranks for each OprName?

SELECT SKYLINE_QUERY_MT.OprID, SKYLINE_QUERY_MT.OprName,
SKYLINE_QUERY_MT.ID, SKYLINE_QUERY_MT.Deliv, (SELECT Count(*)
FROM SKYLINE_QUERY_MT AS X
WHERE X.ID = SKYLINE_QUERY_MT.ID
AND X.Deliv <= SKYLINE_QUERY_MT.Deliv
) AS rank
FROM SKYLINE_QUERY_MT
ORDER BY SKYLINE_QUERY_MT.OprName, SKYLINE_QUERY_MT.ID,
SKYLINE_QUERY_MT.Deliv;











.

Relevant Pages