Updating normal and estimated values in recordset

[awyeah]

Hello everyone,

I have some very difficult task to accomplish using ADODB, or DAO in visual basic 6 whichever. This is my masters project and I am stuck!! Say I have a recordset with 4 customers such as:

Field1        Field2        Field3        Field4        Field5        Field6        Field7
E        N        E        E        N        E        N
E        E        E        N        E        E        N
E        N        E        E        E        N        N
N        N        E        N        N        N        N
 

E=estimated value
N=normal value
 

The number of records are 180,000k (rows), so this cannot be done manually. Basically for rows I wish to convert estimated values into normal values. So row1 is:

Field1        Field2        Field3        Field4        Field5        Field6        Field7
E1        N2        E3        E4        N5        E6        N7
 

CONVERSION
Field 1: Converting field1 to normal value, we have to see the number of estimates before field2, since there is only one, so I need to update in such a way:

Field1        Field2        Field3        Field4        Field5        Field6        Field7
N2/2        N2/2        E        E        N        E        N
 

Then continue to the next estimated element if there is any in that row.
Field 5: Converting field3, field4 to normal value, we have to see the number of estimates after the last normal value I.e. field2 until field 5, since there are 3, so I need to update in such a way:

Field1        Field2        Field3        Field4        Field5        Field6        Field7
N2/2        N2/2        N5/3        N5/3        N5/3        E        N
 

Then continue to the next estimated element if there is any in that row.
Field 7: Converting field6 to normal value, we have to see the number of estimates after the last normal value I.e. field5 until field 7, since there is only 1, so I need to update in such a way:

Field1        Field2        Field3        Field4        Field5        Field6        Field7
N2/2        N2/2        N5/3        N5/3        N5/3        N7/2        N7/2
 

And then continue to the next row, since there is no "E" element and/or the last field of the row was achieved. Then repeat this for all the rows in the recordset.
Provided there is one constraint, if the last value i.e. Field7 is “E” then obviously we cannot normalize the value and update, since there is no “N” value next, so I can use sql to filter those records in that case (which is very frequent).

Basically, my values for E and N in the recodset are such that, I make it a bit easy:

E= “E:<value here>”
N= “N:<value here>”
 

So after I check whether it is “N” or “E” I can split the string with respect to “:” and using the first index, i.e. index=1 to retrieve the value from the string, and can then update it to which ever format I like.

Note the problem is the gaps between "N" and "E" are never fixed always changing, this is causing the main problem. Anyway to tackle this?? Any psuedocode or logic??

Any help regarding this type of problem. I know this is difficult. All help is appreciated.