Hi,
I have a dataset of 750+ records. Each record contains 6 particular numerical information that I need to rank: num1, num2, num3, num4, num5 and num6. After I know the rank each record obtains in each num, I average these 6 rankings, which gives an average rank (avgrk). So far, so good. I have been working with this for more than a year and never had a time out problem.
Now I posted a separated thread yesterday where I explained I wanted to rank each record on its avgrk; that, ultimate, rank (ovrlrk) would have ideally been calculated as a subquery within my same Q1 but a forum user explained me no way, get it done in a q2. My problem here is that my q2 does not respond; my access freeze and I have to crash the process after more than 10min.
Here are my codes:
Q1 called pctlrk
SELECT Q1.ID, Q1.[name], Q1.[Class], Q1.[num1], Q1.[num2], Q1.[num3], Q1.[num4], Q1.[num5], Q1.[num6], (select count(*) FROM tbl1 WHERE [num1] < [Q1].[num1];)+1 AS [num1rk], (select count(*) from tbl1 where [num2] > [Q1].[num2];)+1 AS [num2rk], (select count(*) from tbl1 where [num3] > [Q1].[num3];)+1 AS [num3rk], (select count(*) from tbl1 where [num4] > [Q1].[num4];)+1 AS [num4rk], (select count(*) from tbl1 where [num5] > [Q1].[num5];)+1 AS [num5rk], (select count(*) from tbl1 where [num6] > [Q1].[num6];)+1 AS [num6rk], ([num1rk]+[num2rk]+[num3rk]+[num4rk]+[num5rk]+[num6rk])/6 AS [avgrk]
FROM tbl1 AS Q1;
Q2 called ovrlrk
SELECT [Q2].ID, [Q2].[avgrk], (select count(*) from [ptclrk] where [avgrk] < [Q2].[avgrk];)+1 AS [Overall rk]
FROM pctlrk AS Q2;
For those who successfully pass this test, there is a subsidiary question: how can I have [overall rk] re-inserted back into my Q1 as the last field without creating a circular reference?
Thanks for all comments & suggestions!
Aat