Access hangs on Ranking

[SKV]

I am trying to rank 48000 rcds and on running query Access just hangs.

Any suggestions to overcome this situation? I can reduce my records but dont wat to if possible.

Any other suggestion for ranking these many records?


thanks
SKV

 

[Joe4]

What is the logic for "ranking" them?
What is the need for "ranking" them?

You should be able to sort them in the order you like.
I have had to number things in a certain order before, and I sorted them in the order I like, them used a DAO.Recordset and looped through that query to number them in the order I wanted (using VBA).

 

[SKV]

Joe,

thanks for replying. I have to rank cost for certain groups, so my data looks like
Group Cost Rank
A 200
A 100
B 150
B 400

I guess I can assend my data can you help me write a simple code to put a rank (using counter and reset counter at each group change)

Thanks

 

[Joe4]

Here is a post where I helped someone do something similar. You should be able to use the code I posted there (tailoring it to your needs).

http://www.mrexcel.com/forum/showthread.php?t=479526

 

[SKV]

Thanks I am able to work with Excel thing.

For some reason since I have been trying to rank in access my queries are taking way long time to give an output. is this some memory issue? Same queries use to run very fast before.

 

[Joe4]

I am not sure. I can't say that I have ever really used the Rank function in Access. The script I referenced runs pretty quickly and smoothly.

 

[Norie]

I think ranking 48000 records is bound to take some time.

I don't know what code you are using but it must be comparing the value from one field in one record to the value from the same field of all the other records.