VBA for inserting topvalues in table

[robert909]

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 read another thread on this forum that was coming close, so maybe there's also a VBA-solution for this issue.

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

 

[Ian Mac]

Hi,

Can you provide a bigger sample of data and the outcome you expect from it please?

Cheers,

 

[robert909]

Hi Ian,

Hope it's not too big, but here it comes:

Original table:
Customer Period ReportGroup Sales
507 200508 A 24244
507 200508 B 1920
507 200509 A 24244
507 200509 B 1920
507 200510 A 19318
507 200510 B 360
507 200511 A 19318
507 200511 B 360
507 200512 A 24244
507 200512 B 1920
507 200601 A 24244
507 200601 B 1920
507 200602 A 19318
507 200602 B 360
507 200603 A 19318
507 200603 B 360
510 200508 A 9324
510 200508 B 280
510 200508 C 10
510 200509 A 9324
510 200509 B 280
510 200509 C 10
510 200510 A 17809
510 200510 B 620
510 200510 C 10
510 200511 A 17809
510 200511 B 620
510 200511 C 10
510 200512 A 9324
510 200512 B 280
510 200512 C 10
510 200601 A 9324
510 200601 B 280
510 200601 C 10
520 200508 A 11955
520 200509 A 11955
520 200510 A 23758
520 200510 B 120
520 200511 A 23758
520 200511 B 120
520 200512 A 11955
520 200601 A 11955
520 200603 A 23758
520 200603 B 120

Result table: (so the result of INSERT INTO [NEW TABLE] (SELECT TOP....))
Customer Period ReportGroup Sales
507 200512 A 24244
507 200512 B 1920
507 200601 A 24244
507 200601 B 1920
507 200602 A 19318
507 200602 B 360
507 200603 A 19318
507 200603 B 360
510 200510 A 17809
510 200510 B 620
510 200510 C 10
510 200511 A 17809
510 200511 B 620
510 200511 C 10
510 200512 A 9324
510 200512 B 280
510 200512 C 10
510 200601 A 9324
510 200601 B 280
510 200601 C 10
520 200511 A 23758
520 200511 B 120
520 200512 A 11955
520 200601 A 11955
520 200603 A 23758
520 200603 B 120

Hope it's more clear now. Thanks in advance for your help!

Regards,
Robert