Rank/Number fields

[Keith]

I am trying to rank customers by their total sales. I have a query
'qryInvoiceSalesSummaryMargin' that has the totals for each customer with a
margin percent. I started a new query called
'qryInvoiceSalesSummaryMarginCount' that I would like to rank my customers in
each Profit Center by the 'Price' field. Here is what it looks like now.

SELECT qryInvoiceSalesSummaryMargin.[Profit Center Number],
qryInvoiceSalesSummaryMargin.Terminal, qryInvoiceSalesSummaryMargin.CustNum,
qryInvoiceSalesSummaryMargin.Name, qryInvoiceSalesSummaryMargin.Price,
qryInvoiceSalesSummaryMargin.AvgPrice, qryInvoiceSalesSummaryMargin.[Margin%]
FROM qryInvoiceSalesSummaryMargin
ORDER BY qryInvoiceSalesSummaryMargin.[Profit Center Number],
qryInvoiceSalesSummaryMargin.Price DESC;

 

[KARL DEWEY]

Try this --

SELECT CustNum,Name, Price, (SELECT COUNT(*)
FROM [qryInvoiceSalesSummaryMargin] T1
WHERE T1.Price >= T.Price) AS Rank
FROM [qryInvoiceSalesSummaryMargin] AS T
ORDER BY Price DESC;