XP, MS Acces2000, VBA
I have a table called IMPORT with 1 column that has 75k records. I need to split that table into another table EXPORT with 75 columns and the rows associated with each group. In IMPORT after every 75th record is another group that needs to be its own row in EXPORT. Below is a sample of the data.
IMPORT:
1
2
3
AB
12,500
?
-149
etc.
EXPORT:
1, 2, 3, AB, 12,5000, ?, -149, etc
after the 75th record another row needs to be created/appended
2, 3, 4, BC, 12,6000, ^, 167, etc
I have tried using a crosstab query but I would have to run it 1000 times per group and manually copy the result into EXPORT.
TRANSFORM Min(IMPORT.Field3) AS MinOfField3
SELECT "TEST" AS Expr1
FROM IMPORT
WHERE (((IMPORT.ID)>1 And (IMPORT.ID)<75))
GROUP BY "TEST"
PIVOT IMPORT.Field2;
Is there a way to automate this is using VBA? I am not the best programmer but I am assuming this is the only way to accomplish this task?
Thanks.