Group Ranking Problem...

[Guest]

Hello

I can't figure out how to assign a rank number to groups of records in MS Access
Sample Data

PersonID EventID TimeTake
001 001
001 002
002 001
002 002
001 003
003 003
003 001

Where ID1 and ID2 are composite primary keys. I need a query to rank each person by group of events and then assign a number to their rank temporarily to use in other queries

The result for this query would be
PersonID EventID TimeTaken Ran
001 001 5
001 002 6
002 001 6
002 002 4
001 003 7
003 003 5
003 001 9

This will have about 100 people and events
I will use the rank in other queries
Thanks for your help!

 

[Michel Walsh]

HI,



SELECT a.PersonID, a.EventID, a.TimeTaken, COUNT(*) As rank

FROM myTable AS a INNER JOIN myTable As b
ON a.PersonID=b.PersonID AND a.EventID=b.EventID AND a.TimeTaken >=
b.TimeTaken

GROUP BY a.PersonID, a.EventID, a.TimeTaken



Hoping it may help,
Vanderghast, Access MVP

Hello,

I can't figure out how to assign a rank number to groups of records in MS Access.
Sample Data:

PersonID EventID TimeTaken
001 001 5
001 002 6
002 001 6
002 002 4
001 003 7
003 003 5
003 001 9

Where ID1 and ID2 are composite primary keys. I need a query to rank each

person by group of events and then assign a number to their rank temporarily
to use in other queries.

 

[Guest]

Thanks, but this gives everyone a rank of one since it's GROUP BY a.PersonID, a.EventID, a.TimeTaken. I am trying to group by event only and get it to work with COUNT, but this is hard since only COUNTis an aggregate function. Probably some form of nested SELECTor query of a query or something weird, but I just cant figure it out...

 

[Michel Walsh]

Hi,


Have you tried it? It does NOT return a rank of one to every one.


Assume, for simplicity, there is just one field, f1, three records, a,
b, and c.

SELECT a.f1, b.f1
FROM myTable As a INNER JOIN myTable As b
ON a.f1 >= b.f1



returns


a a
b a
b b
c a
c b
c c


so, it is easy to see that


SELECT a.f1, COUNT(*)
FROM myTable As a INNER JOIN myTable As b
ON a.f1 >= b.f1
GROUP BY a.f1


would return


a 1
b 2
c 3



and should NOT be returning


a 1
b 1
c 1



Hoping it may help,
Vanderghast, Access MVP

Thanks, but this gives everyone a rank of one since it's GROUP BY

a.PersonID, a.EventID, a.TimeTaken. I am trying to group by event only and
get it to work with COUNT, but this is hard since only COUNTis an aggregate
function. Probably some form of nested SELECTor query of a query or
something weird, but I just cant figure it out...

 

[Michel Walsh]

HI,



I see. The Group is rigth, it is the JOIN that is too restrictive, in this
case:


SELECT a.PersonID, a.EventID, a.TimeTaken, COUNT(*) AS rank

FROM RANK_T AS a INNER JOIN RANK_T AS b
ON (a.TimeTaken >=b.TimeTaken) AND (a.EventID=b.EventID)

GROUP BY a.PersonID, a.EventID, a.TimeTaken



where I removed the condition on a.PersonID=b.PersonID



Vanderghast, Access MVP

Hello,

I just tried it and couldn't get it to work.

I have the following structure:

CREATE TABLE RANK_T(
PersonID TEXT(5) NOT NULL,
EventID TEXT(3) NOT NULL,
TimeTaken TEXT(3) NOT NULL,
Primary Key (PersonID, EventID)
);

INSERT INTO RANK_T VALUES ("001", "001", "5");
INSERT INTO RANK_T VALUES ("002", "001", "4");
INSERT INTO RANK_T VALUES ("001", "002", "6");
INSERT INTO RANK_T VALUES ("002", "002", "5");
INSERT INTO RANK_T VALUES ("003", "002", "7");

SELECT a.PersonID, a.EventID, a.TimeTaken, COUNT(*) AS rank
FROM RANK_T AS a INNER JOIN RANK_T AS b ON (a.TimeTaken >=
b.TimeTaken) AND (a.EventID=b.EventID) AND (a.PersonID=b.PersonID)
GROUP BY a.PersonID, a.EventID, a.TimeTaken;

It creates the table with data, but the query returns 1 for each rank. I

just can't figure it out. Sorry, I do not know much about Access.

 

[Guest]

It works great, Thank you very, VERY much!!! You and all the other experts are a great help! And, thanks to Microsoft for providing a comminity for support on their products.