Ranking

[dtaylor]

Hello All - is there a way to rank in a query - similar to rank in excel. i do not see a built-in function in access.

if there is no built in is there a work around? or maybe someone already has a custom function?


Thanks

Dan

 

[mdmilner]

The syntax is TOP

Select TOP 10 field1, field2, field3 FROM tblName ORDER BY field1 DESC;


Mike

 

[dtaylor]

thank you Mike - I will give it a go. Seems very simple.

Thanks

Dan

 

[CT Witter]

If you need to also add ranking identifiers ie 1,2,3 etc. then take a look at MS KB Article#208946 - How to Rank Records Within a Query
http://support.microsoft.com/default.aspx?scid=kb;en-us;208946&Product=acc2000

HTH,
CT

 

[dtaylor]

CT - awsemone! this is exactly what i was looking for!

Mike gave exactly what i asked for in my post...i should of been a little more explicit in my request.....

i need to rank contracts within a customer range..i have thousands of customers that can have from 1 to 20 contracts attached.
man this will save me quite a few headaches... what i have done in the past is take the data out of access and rank in excel ( i built a custom function in excel that ranks within each customer) then brought that data back into access....

Thanks for the heads up and reading between the lines.

Dan

 

[CT Witter]

Your welcome