Sort Top 50

[penndro]

Hi Access Experts,
I know that there are several post in here about RANK functions or RANKING of records. I have viewed and tried several but can not make them work for me. I either get a rank of the same number or a rank in opposite order (lowest to highest).

I have this query result:

Query Name: qryRevenueSummary
Fields:
totalRevenue
Customer Name
Customer Number
Year


I want to be able to rank the TOP 50 REVENUE by largest to smallest.

I created the following query:

SELECT Count(*) AS RNK, *
FROM qryRevenueSummary As A, qryRevenueSummary As B
WHERE A.[TotalRevenue] >= B.[TotalRevenue]
GROUP BY A.[Customer Number], A.[Year]

This is telling me it won't group by * but i did get it to work if I put the field instead - but that brings the numbers smallest to largest.

Please, please help.

Thanks

 

[genomon]

Hw about SELECT TOP 50 with an ORDER BY clause of REVENUE DESC?

Ever notice how fast Windows runs? Me neither.

 

[penndro]

I actually need to see a RANK number as I use it in subsequent queries of this data.

 

[dhookom]

If you want to get the rank of Orders in the Northwind by order of Freight descending, the SQL view is:

SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.Freight, Count(Orders_1.OrderID) AS CountOfOrderID
FROM Orders, Orders AS Orders_1
WHERE (((Orders.Freight)>=[Orders_1]![Freight]))
GROUP BY Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.Freight
ORDER BY Orders.Freight DESC , Orders.Freight DESC;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Simply replace this:
WHERE A.[TotalRevenue] >= B.[TotalRevenue]
with this:
WHERE A.[TotalRevenue] [!]<[/!]= B.[TotalRevenue]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

and don't you want to SELECT the customerNumber and Year info too?

Leslie

Come join me at New Mexico Linux Fest!