Recent content by kriemer

Am I alone in thinking that ranking is way too complicated in Access? I have been trying and trying with no success. Due to my lack of skill I am currently forced to export data to Excel and reimport ranked results to Access. Now the size of my database has grown to the point where Excel can...

What I am looking for is a similarly GUI mechanism for Ranking Queries. Is there any such thing? Thanks for taking the time to respond. k

The attached jpg shows a view of Access Query Designer that I can not seem to duplicate. I am trying to use the Access GUI to build ranking queries but it appears that I first need to get into this view before I can do so. I am using Access 2k3 SP3 so it can't be a function of the version of...

chergh, et al.; And as always, the magic is in the details. Many thanks for the solution and sorry for taking so long to reply. k

I do a lot of rejiggering of the spreadsheets so I have been trying to avoid using the "Auto_Open()" sub name feature. I have found the code below which will open the spreadsheet but doesn't seem to run the macro. In some cases the spreadsheet will close but the macro will not have run at all...

ajetrumpet Now I feel ignorant and rude; I didn't mean to suggest you write my code. What I thought I was asking about was a similar approach using VBS which can run in the host environment. Obviously I was not clear and I apologize. a

Many thanks for your help and speedy reply. Now to re-ask the question as I originally meant to. Is there any way to accomplish this using VBS? No problem hard coding the names into the routine (though you method is certainly slicker). Thanks for your help. k

I have a number of spreadsheets that I would like to: open Spreadsheet #1, run a macro, close, then open Spreadsheet #2, etc. None of the code samples I try work. Typically, the spreadsheet opens, but in some cases the macro does not execute, in other the macro fails. Is there a recommended...

Quacka, Could you post your VBA code. Always interested in working solutions. Thanks in advance. k

I agree, normally 15 minutes is a computer eternity; but this is just one of many scheduled events that take place in the wee hours of the am. With Access providing the ranking I am able to remove an Excel item from the scheduler. I believe that the additional time in Access is more than...

The indexing reduced the run time from forever to about 15 minutes. Wonderful^3; works a treat! Kevin

Indexing sounds like a valuable "trick". 2 remaining questions: 1] Is it possible to index a query result (as would be the case with ABS values)? 2] Your code ranks lowest to highest. How do I rank the highest ABS value as 1, next highest as 2, etc. Many thanks. I hope this has been of...

Craig, How would an index on Date and ABS fields help improve calculation performance? Sorry for being obtuse. Thanks k

Craig, The database is comprised of >65k records, and growing, and so the problem is indeed that the ranking routine takes a long, long, long time to complete. When I filter to reduce the number of records the ranking completes (so I know your code does work). Are there any other ranking...

I'm Flumoxed Craig; I modified your code to suit the specifics of my database (table name, etc.) as follows:SELECT [1 ABS scores to excel].Date/Time, [1 ABS scores to excel].ABS, DCount("*","1 ABS scores to excel","[ABS]<=" & [ABS] & " AND [Date/Time]=#" & [Date/Time] & "#") AS Rank FROM [1...