I'm not sure I understand your subquery.
The SELECT clause need to select someting from Dupe (i.e. something in this
subquery), not from the table in the main query.
I have not idea what <<Expr>> is doing in your FROM clause.
I'm also unclear why you have what seems to be a field name in the FROM
clause.
I don't know if you need Sum() in the WHERE clause.
Sorry: can't make sense of it.
Obviously we can't write your query for you, and we can't see your tables
either.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
mccallen60 said:
Allen Browne said:
See:
Ranking and numbering records
at:
http://allenbrowne.com/ranking.html
Choose the approach that suits you best.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Hello,
I’m trying to create a query that ranks my market volume from highest
to
lowest. The rank needs to be in a separate field. Here’s what I have:
<MKT VOL>
187
3
684
52
205
Here’s what I need:
<MKT VOL> <RANK>
684 1
205 2
187 3
52 4
3 5
Any help is greatly appreciated. Thanks.
Hi Allen,
Thanks for your timely reply. However, I am very confused by your answer
to
my issue. I reviewed your link. According to the Rankin in a Query
example,
it looks as if it first calculates the value in the first query, then
ranks
them in the second query. I figured I needed to utilize the second query
as I
already have the Mkt Vol value. Here's my query:
Rank: (SELECT Count( [08 Top 200 Prescribers - Mkt Volume]![Market Vol
(Apr08-Jun08)] ) AS HowMany
FROM «Expr» [08 Top 200 Prescribers - Mkt Volume]![Market Vol
(Apr08-Jun08)] AS Dupe
WHERE Dupe.TotalValue > [08 Top 200 Prescribers - Mkt Volume]![Market Vol
(Apr08-Jun08)] )
What am I doing wrong? Sorry, I'm an Access novice. Thanks.
