Giving records in query a rank?

[MaddiMond]

Hi,

I need to assign people a rank on how much they sold in comparison to their colleagues. The rank should be placed on the individual persons sales report.

My approach so far was to create a query sorting the records by how much was sold. This query then would be the basis for a report where I include a field with control source = 1 and Running Sum = Over Group. Then I would put a DLookup field into the report of the individual person that looks up the value in the running sum field and displays it.

But then I thought that DLookup probably does not work with reports, only with queries or tables.

There must be a pretty simple way to assign the records in a query a number starting at 1 for the highest amount sold and adding 1 consequently for the other ranks. 1, 2, 3, 4...

I just do not know how to accomplish this? Can anyone help me? Thanks.

Maddi

 

[THWatson]

MaddiMond
Go to the Microsoft Office site and download the sample queries. There is an example in there about how to rank records within a query.

You are correct that one report cannot look up a result in another report using DLookup.

Tom

 

[JerryKlmns]

Not to say that there are many posts here on rank or ranking, to search for

 

[THWatson]

MaddiMond
I can amplify my original post with an example.

If your table is called tblSales, and two fields are Salesperson and Sales, then the query SQL to do the ranking would be...

SELECT tblSales1.Salesperson, tblSales1.Sales, (SELECT Count(*) FROM tblSales WHERE [Sales] < tblSales1.Sales)+1 AS Sort
FROM tblSales AS tblSales1;

Tom