Rank function in queries

[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.

 

[Joe Fallon]

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