@Space Cowboy:
My personal usual limit for determining ranks is around 10,000 records used. Anything above that leads to less than acceptable performance, because the effort increases exponentially as the number of records increases. With 1.7 million records, you're heading for disaster.
The query in #16 looks logical, but it will not be usable in this way given the amount of data.
My tip: do it completely differently.
1) Which backend are you using? Other database management systems such as SQL Server offer more efficient and powerful query execution than Access itself. If you need performance, let the right machine do the work for you.
2) If you are limited to Access: I would split the tasks, save intermediate results and then merge them, i.e. work with temporary tables.
The main goal would be the best possible data reduction for a reasonably suitable ranking.
3) As already mentioned, ranking using queries is systematically complex and therefore problematic. Although many things in a database can be solved better using queries, VBA also comes into play here. In a recordset, such ranks can also be incremented relatively easily using well-presorted records, in one simple run and quite quickly.
So you could combine queries and VBA measures in a suitable procedure.
My personal usual limit for determining ranks is around 10,000 records used. Anything above that leads to less than acceptable performance, because the effort increases exponentially as the number of records increases. With 1.7 million records, you're heading for disaster.
The query in #16 looks logical, but it will not be usable in this way given the amount of data.
My tip: do it completely differently.
1) Which backend are you using? Other database management systems such as SQL Server offer more efficient and powerful query execution than Access itself. If you need performance, let the right machine do the work for you.
2) If you are limited to Access: I would split the tasks, save intermediate results and then merge them, i.e. work with temporary tables.
The main goal would be the best possible data reduction for a reasonably suitable ranking.
3) As already mentioned, ranking using queries is systematically complex and therefore problematic. Although many things in a database can be solved better using queries, VBA also comes into play here. In a recordset, such ranks can also be incremented relatively easily using well-presorted records, in one simple run and quite quickly.
So you could combine queries and VBA measures in a suitable procedure.
Last edited:
