Rank record in query

[RWatson1]

Hello all,

I'm looking for a way of ranking records within a query. Access doesnt have a Rank formula like in excel so any assistance is appreciated.

Thanks

 

[jdraw]

see this

Note: you can find this quite easily via Google or Bing.

 

[plog]

It usually involves a DCount (http://www.techonthenet.com/access/functions/domain/dcount.php) call back into the datasource your query is built on. Essentially, you determine your ranking criteria and then count the number of records that are less than the current record.

If you can be more specific, so can I. Post your table name, relevant fields and explain how you want to rank your data.

 

[pr2-eugin]

Hello RWatson1, yes it does not have one explicitly. However you can still Rank records in Queries !

 

[RWatson1]

Hello Plog,

The table is for a call center environment and displays agent metrics. I have a query that pulls out each key metric in columns. I am trying to create a separate column for each metric that will rank the employees according to the highest value. Here is the sql view:

SELECT [qry_EID_Emp Info].EMP_SHORT_NAME, [qry_EID_Emp Info].EMP_CLASS_1, tbl_ScoreCard.[Report Date] AS [Mnth End], tbl_ScoreCard.[CSAT - OIC], tbl_ScoreCard.[Dispatch Rate - OIC], tbl_ScoreCard.[PSU Yield - OIC], tbl_ScoreCard.[Rev/Call - OIC], tbl_ScoreCard.[Productivity - OIC], tbl_ScoreCard.[72 Hr FCR - OIC]
FROM [qry_EID_Emp Info] INNER JOIN tbl_ScoreCard ON [qry_EID_Emp Info].EID = tbl_ScoreCard.EID;

 

[plog]

That's a little to much to process. I suggest you either follow the links others provided, or post sample data. Include starting data, then what the ultimate data should look like once its ranked. Use this format for posting data:

DataSourceNameHere
Field1Name, Field2Name, Field3Name...
David, 12/13/2004, 18
Sally, 1/12/2009, 44
Tim, 2/26/2012, 51