Ranking

J

Jeff Kaufman

I am currently using Access 2003 and am having trouble getting it to rank one
of my existing query results. Below is my existing query where I want to rank
each of the Team Names by the last field [Total Points]. When I attempt to
use the below query I get an error message that says: "The Specified field
'Q.[Performance Month]' could refer to more than one table listed in the FROM
clause of your SQL statement. Any help would be great.

Query I am attempting to use to rank by total points:

SELECT Q.Department, Q.[Performance Month], Q.[Team Name], Q.[Total Points],
(SELECT COUNT(*) From
[Query_TM_Challenge_Inbound] Q1
WHERE Q1.[Performance Month] = Q.[Performance Month]
AND Q1.[Total Points] < Q.[Total Points])+1 AS Rank
FROM Query_TM_Challenge_Inbound AS Q
ORDER BY Q.[Total Points];

Info comes from the following Query:

SELECT Query_TM_Hierarchy_Current_Month.[Performance Month],
Query_TM_Hierarchy_Current_Month.Department,
Query_TM_Hierarchy_Current_Month.Manager,
Query_TM_Hierarchy_Current_Month.[Team Name], Query_QA_TM_Rank_Inbound_D.[QA
Score], Query_QA_TM_Rank_Inbound_D.Rank, Query_QA_TM_Rank_Inbound_D.Points AS
[QA Points], Query_AHT_TM_Rank_Inbound_D.AHT,
Query_AHT_TM_Rank_Inbound_D.Rank, Query_AHT_TM_Rank_Inbound_D.Points AS [AHT
Points], Query_SignedIn_TM_Rank_Inbound_D.[SignedIn%],
Query_SignedIn_TM_Rank_Inbound_D.Rank,
Query_SignedIn_TM_Rank_Inbound_D.Points AS [Signed-In Points],
Query_Transfer_TM_Rank_Inbound_D.[Transfer%],
Query_Transfer_TM_Rank_Inbound_D.Rank,
Query_Transfer_TM_Rank_Inbound_D.Points AS [Transfer Points],
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.[7_Day_Repeat_Call%],
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.Rank,
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.Points AS [7_Day_Repeat_Call%
Points], Sum([QA Points]+[AHT Points]+[Signed-In Points]+[Transfer
Points]+[7_Day_Repeat_Call% Points]) AS [Total Points]
FROM ((((Query_TM_Hierarchy_Current_Month LEFT JOIN
Query_Transfer_TM_Rank_Inbound_D ON
(Query_TM_Hierarchy_Current_Month.[Performance Month] =
Query_Transfer_TM_Rank_Inbound_D.[Performance Month]) AND
(Query_TM_Hierarchy_Current_Month.[Team Name] =
Query_Transfer_TM_Rank_Inbound_D.[Team Name])) LEFT JOIN
Query_SignedIn_TM_Rank_Inbound_D ON
(Query_TM_Hierarchy_Current_Month.[Performance Month] =
Query_SignedIn_TM_Rank_Inbound_D.[Performance Month]) AND
(Query_TM_Hierarchy_Current_Month.[Team Name] =
Query_SignedIn_TM_Rank_Inbound_D.[Team Name])) LEFT JOIN
Query_QA_TM_Rank_Inbound_D ON (Query_TM_Hierarchy_Current_Month.[Performance
Month] = Query_QA_TM_Rank_Inbound_D.[Performance Month]) AND
(Query_TM_Hierarchy_Current_Month.[Team Name] =
Query_QA_TM_Rank_Inbound_D.[Team Name])) LEFT JOIN
Query_AHT_TM_Rank_Inbound_D ON (Query_TM_Hierarchy_Current_Month.[Performance
Month] = Query_AHT_TM_Rank_Inbound_D.[Performance Month]) AND
(Query_TM_Hierarchy_Current_Month.[Team Name] =
Query_AHT_TM_Rank_Inbound_D.[Team Name])) LEFT JOIN
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D ON
(Query_TM_Hierarchy_Current_Month.[Performance Month] =
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.[Performance Month]) AND
(Query_TM_Hierarchy_Current_Month.[Team Name] =
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.[Team Name])
GROUP BY Query_TM_Hierarchy_Current_Month.[Performance Month],
Query_TM_Hierarchy_Current_Month.Department,
Query_TM_Hierarchy_Current_Month.Manager,
Query_TM_Hierarchy_Current_Month.[Team Name], Query_QA_TM_Rank_Inbound_D.[QA
Score], Query_QA_TM_Rank_Inbound_D.Rank, Query_QA_TM_Rank_Inbound_D.Points,
Query_AHT_TM_Rank_Inbound_D.AHT, Query_AHT_TM_Rank_Inbound_D.Rank,
Query_AHT_TM_Rank_Inbound_D.Points,
Query_SignedIn_TM_Rank_Inbound_D.[SignedIn%],
Query_SignedIn_TM_Rank_Inbound_D.Rank,
Query_SignedIn_TM_Rank_Inbound_D.Points,
Query_Transfer_TM_Rank_Inbound_D.[Transfer%],
Query_Transfer_TM_Rank_Inbound_D.Rank,
Query_Transfer_TM_Rank_Inbound_D.Points,
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.[7_Day_Repeat_Call%],
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.Rank,
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.Points
HAVING (((Query_TM_Hierarchy_Current_Month.Department)="SB EUC Inbound"));
 
K

Ken Snell [MVP]

I don't see two fields with that name in your first query, but perhaps the
presence of a Performance Month field in the GROUP BY clause of that query
is the source of ACCESS' confusion.

Try aliasing the field in your first query:

Query_TM_Hierarchy_Current_Month.[Performance Month] AS PerformanceMonth


And then change your ranking query to use the PerformanceMonth field
instead.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Jeff Kaufman said:
I am currently using Access 2003 and am having trouble getting it to rank
one
of my existing query results. Below is my existing query where I want to
rank
each of the Team Names by the last field [Total Points]. When I attempt
to
use the below query I get an error message that says: "The Specified field
'Q.[Performance Month]' could refer to more than one table listed in the
FROM
clause of your SQL statement. Any help would be great.

Query I am attempting to use to rank by total points:

SELECT Q.Department, Q.[Performance Month], Q.[Team Name], Q.[Total
Points],
(SELECT COUNT(*) From
[Query_TM_Challenge_Inbound] Q1
WHERE Q1.[Performance Month] = Q.[Performance Month]
AND Q1.[Total Points] < Q.[Total Points])+1 AS Rank
FROM Query_TM_Challenge_Inbound AS Q
ORDER BY Q.[Total Points];

Info comes from the following Query:

SELECT Query_TM_Hierarchy_Current_Month.[Performance Month],
Query_TM_Hierarchy_Current_Month.Department,
Query_TM_Hierarchy_Current_Month.Manager,
Query_TM_Hierarchy_Current_Month.[Team Name],
Query_QA_TM_Rank_Inbound_D.[QA
Score], Query_QA_TM_Rank_Inbound_D.Rank, Query_QA_TM_Rank_Inbound_D.Points
AS
[QA Points], Query_AHT_TM_Rank_Inbound_D.AHT,
Query_AHT_TM_Rank_Inbound_D.Rank, Query_AHT_TM_Rank_Inbound_D.Points AS
[AHT
Points], Query_SignedIn_TM_Rank_Inbound_D.[SignedIn%],
Query_SignedIn_TM_Rank_Inbound_D.Rank,
Query_SignedIn_TM_Rank_Inbound_D.Points AS [Signed-In Points],
Query_Transfer_TM_Rank_Inbound_D.[Transfer%],
Query_Transfer_TM_Rank_Inbound_D.Rank,
Query_Transfer_TM_Rank_Inbound_D.Points AS [Transfer Points],
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.[7_Day_Repeat_Call%],
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.Rank,
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.Points AS [7_Day_Repeat_Call%
Points], Sum([QA Points]+[AHT Points]+[Signed-In Points]+[Transfer
Points]+[7_Day_Repeat_Call% Points]) AS [Total Points]
FROM ((((Query_TM_Hierarchy_Current_Month LEFT JOIN
Query_Transfer_TM_Rank_Inbound_D ON
(Query_TM_Hierarchy_Current_Month.[Performance Month] =
Query_Transfer_TM_Rank_Inbound_D.[Performance Month]) AND
(Query_TM_Hierarchy_Current_Month.[Team Name] =
Query_Transfer_TM_Rank_Inbound_D.[Team Name])) LEFT JOIN
Query_SignedIn_TM_Rank_Inbound_D ON
(Query_TM_Hierarchy_Current_Month.[Performance Month] =
Query_SignedIn_TM_Rank_Inbound_D.[Performance Month]) AND
(Query_TM_Hierarchy_Current_Month.[Team Name] =
Query_SignedIn_TM_Rank_Inbound_D.[Team Name])) LEFT JOIN
Query_QA_TM_Rank_Inbound_D ON
(Query_TM_Hierarchy_Current_Month.[Performance
Month] = Query_QA_TM_Rank_Inbound_D.[Performance Month]) AND
(Query_TM_Hierarchy_Current_Month.[Team Name] =
Query_QA_TM_Rank_Inbound_D.[Team Name])) LEFT JOIN
Query_AHT_TM_Rank_Inbound_D ON
(Query_TM_Hierarchy_Current_Month.[Performance
Month] = Query_AHT_TM_Rank_Inbound_D.[Performance Month]) AND
(Query_TM_Hierarchy_Current_Month.[Team Name] =
Query_AHT_TM_Rank_Inbound_D.[Team Name])) LEFT JOIN
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D ON
(Query_TM_Hierarchy_Current_Month.[Performance Month] =
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.[Performance Month]) AND
(Query_TM_Hierarchy_Current_Month.[Team Name] =
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.[Team Name])
GROUP BY Query_TM_Hierarchy_Current_Month.[Performance Month],
Query_TM_Hierarchy_Current_Month.Department,
Query_TM_Hierarchy_Current_Month.Manager,
Query_TM_Hierarchy_Current_Month.[Team Name],
Query_QA_TM_Rank_Inbound_D.[QA
Score], Query_QA_TM_Rank_Inbound_D.Rank,
Query_QA_TM_Rank_Inbound_D.Points,
Query_AHT_TM_Rank_Inbound_D.AHT, Query_AHT_TM_Rank_Inbound_D.Rank,
Query_AHT_TM_Rank_Inbound_D.Points,
Query_SignedIn_TM_Rank_Inbound_D.[SignedIn%],
Query_SignedIn_TM_Rank_Inbound_D.Rank,
Query_SignedIn_TM_Rank_Inbound_D.Points,
Query_Transfer_TM_Rank_Inbound_D.[Transfer%],
Query_Transfer_TM_Rank_Inbound_D.Rank,
Query_Transfer_TM_Rank_Inbound_D.Points,
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.[7_Day_Repeat_Call%],
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.Rank,
Query_7_Day_Repeat_Calls_TM_Rank_Inbound_D.Points
HAVING (((Query_TM_Hierarchy_Current_Month.Department)="SB EUC Inbound"));
Click to expand...
 
Top