Ranking records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a query to report on sales results.
what i would like to do in the query is to rank sales items by total sales
so ex
clothing $500
Jewlery $1000


i would like Jewlery to be ranked as 1 and
clothing as 2
and etc
seems simple but
everything i have tried does not work.
 
Create a Totals query.
Make item Group By
Make Total Sales Sum
Order by Total Sales Descending
 
thanks
how would i pop a field in the query to reflect the ranking?
field would actually have values of 1 2 3 etc in it
 
Schuitkds said:
I have a query to report on sales results.
what i would like to do in the query is to rank sales items by total sales
so ex
clothing $500
Jewlery $1000


i would like Jewlery to be ranked as 1 and
clothing as 2
and etc
seems simple but
everything i have tried does not work.
Click to expand...


Add a calculated field to the query. The general idea is:

Rank: (SELECT Count(*)
FROM table As X
WHERE X.totalsales <= table.totalsales)

It's may be more complicated than that if your query has
grouping and/or criteria.

If you have problems getting it work in your situation,
please post a Copy/Paste of your query's SQL statement.
 
I know it can be done, but I don't know how.

Schuitkds said:
thanks
how would i pop a field in the query to reflect the ranking?
field would actually have values of 1 2 3 etc in it
Click to expand...
 
Back
Top