Automatically rank of data basis a field

**excelhelp18** · 07-09-2011, 09:11 AM

Hi,

In the attached database, column "Rank" is what I am looking for through the query.

I want "Circle" wise ranking on the "Outages" that means If circle "HP" has 8 records ranking would come from 1-8 for these 8 records and if Circle "JK" has 3 records ranking would come from 1-3 for these 3 records. Rank column would not have ranking from 1-11 rather it would be till 8 for circle "HP" and till 3 for cirlce "JK"

Ranking would be based on Outges. Lower the outages, higher the ranking and vice versa.

If ouages were 100, 90, 80 rank would be 3,2,1 respectively.

Kindly help on this.

Thanks in advance.

**alansidman** · 07-09-2011, 10:19 AM

I have done this with a series of queries and a final union query (query5)

Alan

**excelhelp18** · 07-09-2011, 11:59 AM

Thanks for the quick response..

but this does not serve my purpose. I have a large database with about 11 circles and 12 months data for about 10000 sites data.

If I apply these set of queries to each circle, it will be 11 queries and then a union query. It would be very time consuming. I need a simple and easy solution.

If there is some other way out, please help me with the same.

Thanks

Originally Posted by alansidman

I have done this with a series of queries and a final union query (query5)

Alan

**alansidman** · 07-09-2011, 01:42 PM

I can't envision an easy way for this. Do you need to run the query for all 10000 sites or will it be on an adhoc basis? As to the running for months, I would use a search form that would prompt the user for the particular month to query. Unless it is on an adhoc basis, then I might use a list box on a search form to select the particular sites you would wish to have run.

I imagine that you could run a loop through the data set employing VBA, but I cannot help you with that--maybe someone with more VBA experience could show you the way.

Alan

**excelhelp18** · 07-10-2011, 02:45 AM

Thanks for the response.

There is a little modication to the set of data. Same is attached.(Assets1.Zip)

What I am looking for is the ranking of sites basis the outages within the cluster.

KHAGARIA Cluster has 5 sites, hence ranking would be from 1 to 5 within this cluster. similarly, BANKA cluster has 7 sites so ranking from 1-7 should be applicable to sites.
Likewise, BAGAHA cluster has 10 sites so ranking from 1-10 applicable.

Ranking basis the outages should be in the same manner as done earliar..

Lower the outages highest the ranking (10-9-8 etc) and higher the outages, lower the ranking (1,2,3 etc)

Please help..

Originally Posted by alansidman

I can't envision an easy way for this. Do you need to run the query for all 10000 sites or will it be on an adhoc basis? As to the running for months, I would use a search form that would prompt the user for the particular month to query. Unless it is on an adhoc basis, then I might use a list box on a search form to select the particular sites you would wish to have run.

I imagine that you could run a loop through the data set employing VBA, but I cannot help you with that--maybe someone with more VBA experience could show you the way.

Alan