Select count (*) from within current query

[aat]

Hi,

I have a query where I put some rankings on the basis of fields taken from table A (say). So the first ranking returns the position based on figure x from A and the second ranking returns the position based on figure y from A. Now I need to do an average of these two rankings, fine, and I need to rank these averages. So if record one as a better average than record 2, ideally this field in my query should return 1 for rec1 and 2 for rec2. For convenience I need the three fields (rankings of average y, ranking of average x, and ranking of average rankings) into the same query because that gives a lot of information visually.



My ranking on x and y work finely, but my select count (*) on the average rankings does not:
(select count(*) where [average rk] < [rankings].[average rk]+1 AS [Overall rk]

Could someone help me understand why?

Thank you
Aat

[Dal Jeanis]

There are hundreds of possible ways to get that kind of compound ranking query wrong. You'll probably need to post all three queries to get any reasonable guess at what's wrong, let alone a definitive answer.

First, make sure that each record is receiving exactly one answer from qryRank1 and qryRank2.

Second, make a second, single query that combines the two results and gets you the average. If the first two ranks are able to be combined into a single query and get the average at the same time, that's fine too.

Third, write a third query to link that query back to itself to calculate the blended ranking. Do not try to calculate it within the second query, because the information to calculate it doesn't exist there.

[aat]

Hi Dal
Thanks. Basically, my field on rank1 and rank2 return the appropriate results, and my field average rk ((rank1 + rank2) / 2) also returns the appropriate result. These are three fields within one query.
Now I need to add a last field that count, in that one query, the number of records were average rk is lower (better), and that is my overall rk.

For my purpose here really rank1 works, rank2 works, average rk works but my overall rk (formula pasted in my first post) does not..

Thanks for your suggestion.

Aat

[Dal Jeanis]

Okay, so assuming you have a working query that returns the key field and your three ranks in this sort of layout:

Query:  qryRanks 
     MyKey,
     Rank1,
     Rank2,
     (Rank1 + Rank2)/2 AS RankAvg

Then this gives you your third rank

Query:  qryRanks3A
  SELECT 
     Q1.MyKey,
     First(Q1.Rank1) As Rank1,
     First(Q1.Rank2) As Rank2,
     First(Q1.RankAvg) As RankAvg,
     Count(Q2.MyKey) As Rank3
  FROM 
     qryRanks AS Q1,
     qryRanks AS Q2
  WHERE 
     Q1.RankAvg <= Q2.RankAvg
  GROUP BY Q1.MyKey;

or this should work as well:

Query:  qryRanks3B
  SELECT 
     Q1.MyKey,
     Q1.Rank1,
     Q1.Rank2,
     Q1.RankAvg,
     (SELECT Count(*) FROM qryRanks AS Q2
      WHERE Q1.RankAvg <= Q2.RankAvg) AS Rank3
  FROM 
     qryRanks AS Q1;

The second should be more efficient in this case.

You may need to flip the sign on the compare, if the resulting ranks are upside down.

[aat]

Thanks Dal,
So if I understand you correctly there is no way I can have all these fields in the same query.
So OK I created my second query on the model of your second option (thanks for all the writing by the way), now I realise that it is taking forever for Access to return my overall rk = I had to stop the process after more than 20 mins of program not responding. Ooops.
But OK I got the principle of it, I suppose the key finding here is I have to do two queries.
Thanks again
Aat

[Dal Jeanis]

aat - that's because your prior query is using Count(*), which is often very inefficient for large datasets. If you post your initial query, I can help you tighten it up.

[aat]

Hi Dal
I have a circular reference now between my Q1 (rankings + average rank) and my Q2 (ranking of average ranks)... As I attempted to show the ranking of the average ranks back into my Q1. Any chance I can break that?
Thanks
Aat

[Dal Jeanis]

A guy goes to a doctor and says, "Doc, it hurts when I do *this*."

Doctor squints at him, shakes his head, and says, "Don't do that."

There's no reason to put the results of Q2 back into Q1. You pull the desired results of Q1 into Q2, and you have all the results you need in Q2.