Ranking

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex - 27 May 2008 19:52 GMT

I am trying to rank using the following query:

SELECT Emp1.DFWeek, (Select Count (*) from qry_Defects_by_Oper_Shift_Chart
Where [DFWeek] < [Emp1].[DFWeek])+1 AS Seniority
FROM qry_Defects_by_Oper_Shift_Chart AS Emp1
ORDER BY Emp1.DFWeek DESC;

My query is ranking the DFWeek field, but not correctly.

I have a field DFWeeks, for example 18, 17, 16, 15. I need that field
ranked 18 = 1, 17 = 2, 16 = 3, 15 = 4. The same query may return 8, 9, 10,
11 and I'd need those ranked 11 = 1, 10 = 2, 9 = 3, 8 = 4. Any idea how I
can revise my query to work correctly? Thank you much.

Reply to this Message

Michel Walsh - 27 May 2008 20:53 GMT

Change the < to a >

Vanderghast, Access MVP

>I am trying to rank using the following query:
>
[quoted text clipped - 11 lines]
> 11 and I'd need those ranked 11 = 1, 10 = 2, 9 = 3, 8 = 4. Any idea how I
> can revise my query to work correctly? Thank you much.

Reply to this Message

Alex - 27 May 2008 21:18 GMT

No, the problem is that my DFWeek field and ranking are as follows and I
can't figure out why:

DFWeek Seniority
18 1
17 6
16 17
15 25

I need:
DFWeek Seniority
18 1
17 2
16 3
15 4

> Change the < to a >
>
[quoted text clipped - 15 lines]
> > 11 and I'd need those ranked 11 = 1, 10 = 2, 9 = 3, 8 = 4. Any idea how I
> > can revise my query to work correctly? Thank you much.

Reply to this Message

Michel Walsh - 27 May 2008 21:38 GMT

You may have duplicated values in qry_Defects_by_oper_shift_chart.

A possible solution would be to make another query:

SELECT DISTINCT DFWeek FROM qry_Defects_by_oper_shift_chart

save it, say, under the name q1, then

SELECT Emp1.DFWeek,
(SELECT COUNT (*) FROM q1
WHERE [DFWeek] < [Emp1].[DFWeek])+1 AS Seniority
FROM qry_Defects_by_Oper_Shift_Chart AS Emp1
ORDER BY Emp1.DFWeek DESC;

If that does not work, can still use a join rather than a sub-query:

SELECT a.DFWeek, 1+COUNT(a.DFWeek) AS Seniority
FROM qry_Defects_by_Oper_Shift_Chart AS a
LEFT JOIN q1 ON q1.DFWeek < a.DFWeek
GROUP BY a.DFWeek

Vanderghast, Access MVP

> No, the problem is that my DFWeek field and ranking are as follows and I
> can't figure out why:
[quoted text clipped - 33 lines]
>> > how I
>> > can revise my query to work correctly? Thank you much.

Reply to this Message