Ranking

[Alex]

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.

 

[Michel Walsh]

Change the < to a >

Vanderghast, Access MVP

 

[Alex]

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 >

Vanderghast, Access MVP

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.

 

[Michel Walsh]

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:

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 >

Vanderghast, Access MVP

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.