Originally Posted by
hoachen
Group# |
InvName |
Value |
GroupName |
1234 |
BS11 |
10.5 |
aabb |
1234 |
BS11 |
10.5 |
ccdd |
1234 |
BS4 |
5.5 |
hhee |
1234 |
BS11 |
10.5 |
ooppp |
1235 |
BC77 |
3.2 |
jjii |
Group# |
InvName |
Value |
GroupName1 |
GroupName2 |
GroupName3 |
1234 |
BS11 |
10.5 |
aabb |
ccdd |
ooppp |
1234 |
BS4 |
5.5 |
ccdd |
|
|
1235 |
BC77 |
3.2 |
jjii |
nncc |
|
Based on above Table & the results that you want, Just check out if below gives some guidelines :
Save below query
qryRanking
Code:
SELECT
A.TheGroup,
A.InvName,
A.TheValue,
A.GroupName,
Count(*) AS Rank
FROM
Table1 AS A
INNER JOIN
Table1 AS B
ON
(A.GroupName>=B.GroupName)
AND
(A.InvName=B.InvName)
AND
(A.TheGroup=B.TheGroup)
GROUP BY
A.TheGroup,
A.InvName,
A.TheValue,
A.GroupName;
And
Run the below final query :
Code:
TRANSFORM First(qryRanking.GroupName) AS FirstOfGroupName
SELECT
qryRanking.TheGroup,
qryRanking.InvName,
qryRanking.TheValue
FROM
qryRanking
GROUP BY
qryRanking.TheGroup,
qryRanking.InvName,
qryRanking.TheValue
PIVOT qryRanking.Rank;
Edit :
Pls note : Ranking queries are tricky (at least, I find them so). Be careful.
Thanks