Hello again. I have the following query:

SELECT tblProductInfo.strItemNumber, tblProductInfo.strDescription, [Units]*[lngAmtPerCase] AS [Total Units]
FROM tblProductInfo INNER JOIN tblLastYearSales ON tblProductInfo.strItemNumber = tblLastYearSales.[Item #];

I'd like to add a Rank column that will rank the items. The item that has the most [Total Units] would be ranked 1, and descending from there. I've tried a couple of things, but I obviously didn't do it right. I thought it would be pretty easy, but I couldn't figure it out (doesn't mean it's not easy).

Kevin

How about
1) Sorting in descending order of Total Units.
2) Adding a calculated field which is always 1.
3) Creating a running total of that calculated field.

Problems:
a) You'd have to look up on the Microsoft Knowledge Base how to calculate a running total in a query (unless you do this in a report).
b) People with exactly the same Total Units would not show as being of equal ranking.

Good Morning, Kevin.

Check out this thread or this one.

HTH