Rank function in queries

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Is there a function that lets me add a ranking in a query
similar to the Rank() function in Excel? I would like my
query to output all 5 columns of data with an additional
column next to each of those columns that shows how that
unit ranks on that column. I'm using Access 2000.
 
No.
There is not.

You have to use SQL code. There is a lot of info on this in Google.

This is one way to do it:

This SQL statement will give you both a record number and a running total of
freight in the Orders table. Modify it to meet your needs after testing it
on Northwind.

SELECT (SELECT COUNT(OrderID) FROM Orders AS temp WHERE temp.OrderID <=
Orders.OrderID) AS Recno, Orders.OrderID, Orders.Freight, (SELECT
Sum(Freight) FROM Orders AS temp WHERE temp.OrderID <= Orders.OrderID) AS
RunningTotal
FROM Orders
ORDER BY Orders.OrderID;


For another solution check out this MSKB Article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210554
 
Back
Top