Matrix addition in VBA? Or summing cells...

[MePenguin]

I have a situation where I want to add the values from blocks or rows of a table together, cell wise - matrix arithmetic, basically.

I have a functioning system using queries (SUM and GROUP BY), and a few tables which are emptied and refilled during the calculations. The calculations are iterative, so that as the program runs, Access2000 grows - so much so that the mdb can grow from 15Mb to over 1.5Gb!
I suppose I could write code to pause the calculations and compact the mdb when a file size is reached, but the calculations take long enough as it is.

Some details:

Table: TbirmBEETLEdat contains 62 field, and 15696 records.
The fields are all number format, as follows:

 - (double), identifier for each matrix block of 36 records (GROUP BY in the SQL)
[MCRRow] - (integer), identified for each record within each block of CODEs.
[Field1] to [Field60] - (integer) the data matrix (consisting of 1's and 0's that represent the thermal envelopes of beetle species identified by their CODE)

Something like this:
[code]
Field1,	Field2,	Field3,	... 	MCRRow,	CODE
0,	0,	0,		1,	1.001002
0,	0,	0,		2,	1.001002
0,	0,	1,		3,	1.001002
0,	1,	1,		4,	1.001002
...
0,	0,	0,		1,	6.023231
0,	0,	1,		2,	6.023231
0,	1,	1,		3,	6.023231
0,	1,	1,		1,	6.023231
0,	0,	1,		1,	6.023231
.
.
.
0,	0,	1,		36,	6.023231
etc..

I am hoping to avoiding having to loop through each individual value and add it to the sum of the previous matrices, since that sounds slow...
The psuedo(ish)code I have so far is (which does exactly that):

For SpeciesLoop = 1 To UBound(ArraySpList) 'ArraySpList is passed to the sub, 
	'and contains the CODEs of the species to be used
    
    'Construct SQL to get current species
    SQLText = "SELECT TbirmBEETLEdat.* FROM TbirmBEETLEdat WHERE (((TbirmBEETLEdat.CODE)="
    SQLText = SQLText & ArraySpList(SpeciesLoop) & "));"
    
    'retrieve matrix for current species as recordset
    Set rsBeetledat = db.OpenRecordset(SQLText, dbOpenSnapshot) 
    
    'put the recordset into an array
    Set ArrayMCRGrid = rsBeetledat.GetRows(36)

	'This is the point where I am about to put in a couple of loops:
	For MatrixY = 1 to 36	'loop records
		For MatrixX = 1 to 60	'loop fields
		
		'and the maths goes in here
		tempArray(MatrixX,MatrixY) = tempArray(MatrixX,MatrixY) + ArrayMCRGrid(MatrixX,MatrixY)

		Next MatrixX
	Next MatrixY

Next SpeciesLoop

Help anyone?

thanks,

Phil

Long post, sorry. More info, code examples can be provided if it helps. This is part of quite a complex routine, but I could upload code onto a webserver if people want to see it...

 

[MePenguin]

OK, I realised that that was a very long and confusing question! Turns out that looping the arrays was pretty fast anyway.
Problem solved.