SELECT Q.Department, Q.[Performance Month], Q.[Team Name], Q.[STAT], (SELECT
COUNT(*) From [Query_Current_Month] Q1 WHERE Q1.[Performance Month] =
Q.[Performance Month] AND Q1.[STAT] < Q.[STAT])+1 AS Rank,
Query_Metrics_Points.[Total Points Possible]
FROM Query_Current_Month AS Q LEFT JOIN Query_Metrics_Points ON
Q.[Performance Month] = Query_Metrics_Points_.[Performance Month]
ORDER BY Q.[STAT];
I want to add another field "Point Results" and I want to calculate the
Point Results by taking highest value listed in the rankings field +1 and
then subtracting the ranking then taking that results and multipling it by
the Total Possible points divided by the highest ranking number.
So in my example below... for Team 1... their Point results would be (Highes
Ranking Number (3) +1 minus team 1 rank (1)) divided by (Possible points (20)
divided by Highes Ranking Number (3)) and the results would return 20
My query right now looks like?
Dept Month Team Stat Rank Possible Points Point Results
Department 1 June 2009 Team 1 25.89 1 20 20
Department 1 June 2009 Team 2 26.67 2 20 13
Department 1 June 2009 Team 3 26.74 3 20 7
KARL DEWEY said:
I do not follow. Post your SQL and tell me where you would want to add the
formula.
--
Build a little, test a little.
Jeff Kaufman said:
Great... now I am attempting to add one more thing to my query. I have added
another field called Possible Points. I know want to add the following
formula into my query to be able calculate Point Results
(((Total number of groups +1) Minus my Ranking) divided by the total number
of groups)*possible points = Point Results
So for example if I had 35 groups... and the possible points were 20... and
I ranked number 4... so my point value is 18.285714
:
Sorting is based on the GROUP BY unless you add ORDER BY which I did not.
You can also have --
WHERE Q1.[Month] = Q.[Month]
AND Q1.[Grouping] = Q.[Grouping]
AND Q1.[Stat] <= Q.[Stat])+1 AS Rank
--
Build a little, test a little.
:
okay.. thanks that helps alot. So to understand completly...
The statement "WHERE Q1.[Grouping] = Q.[Grouping]" is telling it the column
to rank by
and the "AND Q1.[Stat] < Q.[Stat])+1 AS Rank" is the numeric value to rank
And if I want to change the order it ranks I simply change the < to >
So if I wanted to sort it by everyone in that month I would change "WHERE
Q1.[Grouping] = Q.[Grouping]" to WHERE Q1.[Month] = Q.[Month]
Thanks
________________________________________________________________
:
No, " [Query_Current_Month] Q " gives your query an alias of Q and "
[Query_Current_Month] Q1 " gives your query an alias of Q1.
--
Build a little, test a little.
:
Hello all,
Okay I am a beginner at this... so everywhere you have the "Q." listed,
should I be replacing that with my current Quere name? Where you have "Q1."
listed what should I be replacing that with?
Thanks for all your help
:
Try this --
SELECT Q.[Month], Q.Grouping, Q.Stat, (SELECT COUNT(*) FROM
[Query_Current_Month] Q1
WHERE Q1.[Grouping] = Q.[Grouping]
AND Q1.[Stat] < Q.[Stat])+1 AS Rank
FROM Query_Current_Month AS Q
ORDER BY Q.[Month], Q.Grouping, Q.Stat;
--
Build a little, test a little.
:
Hello All - I am currently using Access 2003. I have a query set up so that
I can get information for a stat for each month by group. I would like to be
able to have access also show another field in my query results that would
rank the groups from 1-50 based on the number of groups in the query result
with ties getting ranked the same. Currently I taking the results of the
query and exporting to excel and using the Rank() function to have it rank my
results for me.
Here is my SQL for my current query
SELECT Query_Current_Month.[Month], Query_Current_Month.[Grouping],
Query_Current_Month.[Stat]
FROM Query_Current_Month
