Ranking Records Using VBA Module In A Query (1 Viewer)

[The Brown Growler]

Hi,

I am looking for a more eficcient way to rank some customer orders using the time of the order per day for the ranking. I can get it to rank OK in a query using an "Alias" option but for large record sets it takes hours and hours to run.

Is it possible to create a vba module to do this more efficiently via a custom ranking function or something similar? An example of what I wish to achieve is pasted below

table named t_orders, fields named [Date], [Customer],[OrderTime]

15/03/2012, Compaq, 09:45
15/03/2012, Compaq, 11:14
15/03/2012, Compaq, 16:30
15/03/2012, NEC, 09:20
15/03/2012, NEC, 10:10
15/03/2012, NEC, 15:40
18/03/2012, Compaq, 10:50
18/03/2012, Compaq, 12:42
18/03/2012, Compaq, 14:10
18/03/2012, NEC, 11:20
18/03/2012, NEC, 12:18
18/03/2012, NEC, 15:11

After the application of the ranking the [OrderRank] values are the rightmost figure on each line

15/03/2012, Compaq, 09:45 1
15/03/2012, Compaq, 11:14 2
15/03/2012, Compaq, 16:30 3
15/03/2012, NEC, 09:20 1
15/03/2012, NEC, 10:10 2
15/03/2012, NEC, 15:40 3
18/03/2012, Compaq, 10:50 1
18/03/2012, Compaq, 12:42 2
18/03/2012, Compaq, 14:10 3
18/03/2012, NEC, 11:20 1
18/03/2012, NEC, 12:18 2
18/03/2012, NEC, 15:11 3

The ranking is assigned on the basis of [OrderTime] per [Customer] on that [Date] with the earliest order assigned the value of 1 and increasing with subsequent orders per customer on that date.


Rgds
Growlos

 

[VilaRestal]

I find it hard to believe a VBA function would work quicker.

And hours and hours is surprising even on a huge table.

Are all those fields indexed?

And is the underlying query order by those fields? It might give it a helping hand to base it on a subquery that is. I'm not sure whether it would be best to order by [Date], [Company], [Time] or [Time], [Company], [Date] if it makes any difference at all.

 

[The Brown Growler]

I find it hard to believe a VBA function would work quicker.

And hours and hours is surprising even on a huge table.

Are all those fields indexed?

And is the underlying query order by those fields? It might give it a helping hand to base it on a subquery that is. I'm not sure whether it would be best to order by [Date], [Company], [Time] or [Time], [Company], [Date] if it makes any difference at all.

Many thanks, the fields were not indexed and by changing the status to indexed it runs very quickly. I did not realise that indexing the fields would have such an impact on performance.

All resolved, many thanks once again for pointing me in the direction of indexing.


Rgds
Growlos

 

[VilaRestal]

You're welcome, glad it did the trick