How to do this in VB using SQL?????

[Gjcollie]

This has stumped me for a while, so I am hoping that someone here has an efficient way to do this using SQL from VB.

Basically what I want to do is average certain fields in the 1st and 2nd record of a recordset and output the result to a new recordset. Then do the same for the 1st and 3rd, 1st and 4th, 1st and 5th and so on...until I reach the end of the recordset. The process then needs to repeat itself with the 2nd and 3rd, 2nd and 4th etc etc etc. until it has iterated through every record in the recordset.

Right now the program is outputing the averages to an array one record at a time then adding the data to new recordset...aga in one record at a time. The process is painfully slow, I am hoping that there is a more efficient way to do it.

Oh by the way it is an Access database and I am using VB 6.

Any suggestions are welcome.

Thanks

[nev]

dim i, j, low, high as integer
dim ave as float

for i = 0 to rs.count - 1
low = val(rs.item("ce rtainfield", i).value)
for j = 0 to rs.count -1
high = val(rs.item("ce rtainfield", i).value)
ave = (low + high) / 2
'code here to place ave in your new recordset
next
next

'Note: Haven't used vb6 for quite sometime. I use vb 2005. I don't know if the syntax is correct.

[Killer42]

One possibility might be to write your results to a text file, then import that to the database.

How many records are you talking about? And is this a one-off job, or will it be run regularly? Do you need to update existing records in the "new" dataset, or will you be creating it each time, or what?