group by query in vba

[Benjaminvh]

Hi guys.

Does anyone know how to run a make table/group by query from vba?

What I want to do it run a loop through, say, 100000 records. This creates a new table with some added calculated fields. After these 100 000 records, I want the code to make a table with grouping for a selection of variables and summing for the last. Then I want the code to start from record 100 001 and repeat the procedure through record 200 000, make a grouped by table and append this table to the previous table. Then again for 200 001 to 300 000 etc until EOF.

I have the code that will perform the calculations and it works fine, if a little inefficiently. There are millions of records in the priginal table I'm working with, and the code (necessarily) creates a new table with many many more records in it, hence the need to group every now and again.

Here is the SQL string:

SELECT TEMP_KN_DATA.PRODUCT_CODE, TEMP_KN_DATA.POL_TYPE, TEMP_KN_DATA.PRM_STATUS, TEMP_KN_DATA.SEX, TEMP_KN_DATA.IRP_MKR, TEMP_KN_DATA.SMOKER_STAT, TEMP_KN_DATA.OCC_CLASS, TEMP_KN_DATA.CALENDAR_YEAR, TEMP_KN_DATA.DURATION, TEMP_KN_DATA.AGENEXTBIRTHDAY, Sum(TEMP_KN_DATA.EXPOSURE) AS SumOfEXPOSURE INTO THE_BIG_TABLE
FROM TEMP_KN_DATA
GROUP BY TEMP_KN_DATA.PRODUCT_CODE, TEMP_KN_DATA.POL_TYPE, TEMP_KN_DATA.PRM_STATUS, TEMP_KN_DATA.SEX, TEMP_KN_DATA.IRP_MKR, TEMP_KN_DATA.SMOKER_STAT, TEMP_KN_DATA.OCC_CLASS, TEMP_KN_DATA.CALENDAR_YEAR, TEMP_KN_DATA.DURATION, TEMP_KN_DATA.AGENEXTBIRTHDAY;

Have no idea what to do with it.

Please help!