help tweak this rank query please!!!!!!

[rfuscjr via AccessMonster.com]

I am close but can not get my query right.

SELECT
junk2.FiscalYear,
junk2.DEPARTMENT_NAME,
junk2.Employee_Number,
junk2.CountOfRecords,
(Select Count(*) from junk Where [countofRecords] > junk2.[countofRecords]+1
AS Expr1

FROM JUNK AS junk2

GROUP BY junk2.FiscalYear,
junk2.DEPARTMENT_NAME,
junk2.Employee_NUMBER,
junk2.CountOfRecords;

This essentially ranks ALL rowss based on CountofRecords.

So I can see which row has the most records, the second most, third most etc
etc.


What I need is to see this by Fiscal Year then Dept. In other works rank the
record counts base on year and dept therein. Its ranking them for ALL years
ALL Depts right now.

Ideas?

 

[John Spencer]

The expression should probably be

(Select Count(*) from junk as J
Where J.[countofRecords] > junk2.[countofRecords]+1
AND J.FiscalYear = Junk2.FiscalYear
and J.Department_Name = Junk2.Department_Name) AS Expr1


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[rfuscjr via AccessMonster.com]

Thanks John....will try!

The expression should probably be

(Select Count(*) from junk as J
Where J.[countofRecords] > junk2.[countofRecords]+1
AND J.FiscalYear = Junk2.FiscalYear
and J.Department_Name = Junk2.Department_Name) AS Expr1

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am close but can not get my query right.

[quoted text clipped - 23 lines]

Ideas?

 

[rfuscjr via AccessMonster.com]

does not appear to work...I do not get ranks: 1,2,3,4 etc...more like: 1,4,
13,15,66...not sure what the heck is going on....

Thanks John....will try!

The expression should probably be

[quoted text clipped - 15 lines]

 

[John Spencer]

Perhaps you can describe what you are attempting to do with this query.

You might try something like the following query named as qJunk. It
eliminates the duplicate values and therefore you will eliminate all the
duplicates when you do the counting.

SELECT DISTINCT
junk2.FiscalYear,
junk2.DEPARTMENT_NAME,
junk2.Employee_Number,
junk2.CountOfRecords,
FROM JUNK

THEN you use that query as a source for another query
SELECT
FiscalYear,
DEPARTMENT_NAME,
Employee_Number,
CountOfRecords,
(Select Count(*)
from qJunk as Q2
Where Q2.[countofRecords] > qJunk.[countofRecords]
AND Q2.FiscalYear = qJunk.FiscalYear
AND q2.Department_Name = qJunk.Department_Name
)+1 AS Expr1
FROM qJunk



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

does not appear to work...I do not get ranks: 1,2,3,4 etc...more like: 1,4,
13,15,66...not sure what the heck is going on....

Thanks John....will try!

The expression should probably be

[quoted text clipped - 15 lines]

Ideas?

 

[rfuscjr via AccessMonster.com]

Thaks John, I should have provided an explicit example....here is a sample
file, lets call it tblData:

FY Dept EmpId QtySold
2008 AAA 111 150
2008 AAA 222 75
2008 AAA 333 225
2008 BBB 111 60
2008 BBB 444 97
2008 BBB 666 111
2009 AAA 898 600
2009 AAA 220 456
2009 AAA 330 1010


I want to query it and roll it up into the following; adding a new column:
Rank:

FY Dept EmpId QtySold Rank
2008 AAA 333 225 1
2008 AAA 111 150 1
2008 AAA 222 75 2
2008 BBB 666 111 1
2008 BBB 444 97 2
2008 BBB 111 60 3
2009 AAA 330 1010 1
2009 AAA 898 600 2
2009 AAA 220 456 3


Thoughts....

Perhaps you can describe what you are attempting to do with this query.

You might try something like the following query named as qJunk. It
eliminates the duplicate values and therefore you will eliminate all the
duplicates when you do the counting.

SELECT DISTINCT
junk2.FiscalYear,
junk2.DEPARTMENT_NAME,
junk2.Employee_Number,
junk2.CountOfRecords,
FROM JUNK

THEN you use that query as a source for another query
SELECT
FiscalYear,
DEPARTMENT_NAME,
Employee_Number,
CountOfRecords,
(Select Count(*)
from qJunk as Q2
Where Q2.[countofRecords] > qJunk.[countofRecords]
AND Q2.FiscalYear = qJunk.FiscalYear
AND q2.Department_Name = qJunk.Department_Name
)+1 AS Expr1
FROM qJunk

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

does not appear to work...I do not get ranks: 1,2,3,4 etc...more like: 1,4,
13,15,66...not sure what the heck is going on....

[quoted text clipped - 5 lines]

[quoted text clipped - 15 lines]
Ideas?

 

[rfuscjr via AccessMonster.com]

I think I got it....The data was a bit more complicated than below and
trouble arose when there were numerous records that 'tied' for a particular
rank. I had to essentially group on FY/Dept/Qty (eliminating EmpId) into a
new table. Then do the rank with that table. Then link that table back to
the original table keying on FY/Dept/Qty and 'assign' the rank back to the
original records...now including those where there was a 'tie'. Hope this
makes sense.

Thaks John, I should have provided an explicit example....here is a sample
file, lets call it tblData:

FY Dept EmpId QtySold
2008 AAA 111 150
2008 AAA 222 75
2008 AAA 333 225
2008 BBB 111 60
2008 BBB 444 97
2008 BBB 666 111
2009 AAA 898 600
2009 AAA 220 456
2009 AAA 330 1010

I want to query it and roll it up into the following; adding a new column:
Rank:

FY Dept EmpId QtySold Rank
2008 AAA 333 225 1
2008 AAA 111 150 1
2008 AAA 222 75 2
2008 BBB 666 111 1
2008 BBB 444 97 2
2008 BBB 111 60 3
2009 AAA 330 1010 1
2009 AAA 898 600 2
2009 AAA 220 456 3

Thoughts....

Perhaps you can describe what you are attempting to do with this query.

[quoted text clipped - 35 lines]

[quoted text clipped - 15 lines]
Ideas?