insert Topvalues in table with VBA 1

[robertsquestion]

Hi,

I'm using Access 2003 and have a question about "topvalues".

I've created table T_Sales with following fields:
Customernr
Period
Productgroup
Sales

Suppose we've only sold in Productgroup A, B and C. Following example records could be in the table:
1000 - 200701 - A - 850
1000 - 200701 - B - 910
1000 - 200703 - A - 603
1020 - 200702 - B - 880
1050 - 200701 - A - 960
1050 - 200701 - C - 320

What I would like to have is the Top 4 (= last 4) periods that a customer has sales in (together with the other fields in table T_Sales). I've tried to solve this with sub-queries, but that is quite slow. I understood that it might be faster with VBA, but I don't know the exact code.

I think a good solution would be if I could use something like "INSERT INTO (SELECT TOP....). So I would like to insert the TOP4 in a new table and use that for analysis.

But my problems are:
- there are around 10000 - 15000 records in the table. Using "TOP N" in a query slows things pretty much down. So using VBA might solve this.
- I first need to group on the combination of Customer and Period (because a customer could have 3 or more rows in one period) and then select Top 4.
- not all customers have sales in all periods. So Top 4 periods could differ per customer. (so with Top 4 periods I mean the last 4 periods and not the 4 periods with the largest sales)

Is there anyone who knows a VBA-solution for this?
Thanks in advance for your help!

Regards,
Robert
The Netherlands

 

[MajP]

99 times out of 100 sql will be much faster than looping through a recordset in vb. And from what you show, I can not see of any way in VBA besides looping through the recordset. Sometimes if you can write an algorithm that searches for a few items and kicks out once these items are found you might be able to beat sql. I would post your SQL on the Access Query Forum and work on optimizing your query. What you are asking is a very standard query and I am very suprised that with only 10k records you are grinding to a halt. I am not a query expert, but having the proper indices will be key and can make a huge difference. I would think that that period, customer, quantity should all be indexed. Here is an article

http://support.microsoft.com/kb/209126

but there are hundreds of articles on improving query speed.