Sum Array from Database

[Erroneous]

Hello everyone!

I have a database field that stores values like this format "1,3,1,6,0,2,7"

Now i want to perform a SUM function on that field.
For example, the query found 3 results so lets say the values are:

    Field 1   |    Field 2
------------  | ------------
2,1,0,1,1,5,2 |
0,0,5,4,0,0,3 |
1,1,4,0,0,1,0 |
------------  | ----------
3,2,9,5,1,6,5  << expected output

I want to perform a sum function where the output would be like that.


Here is what i started i don't know if it's correct.

Dim myArr() As String
Dim tmp(6) As String

For y = 1 To rs.RecordCount
       
    myArr = Split(rs!FieldFromDbase, ",")
    tmp(y - 1) = tmp(y - 1) + myArr(y - 1)
    rs.Movenext

Next y

There is a flaw in this code. Can somebody help me modify this so that i can sum each element in the array?

[baja_yu]

2,1,0,1,1,5,2... So this is one record with 7 fields/table columns? And you want to sum the values of each column across all records (rows)?

If so, you can use the SUM function, like: SELECT SUM(column_name) FROM table_name

More info here: http://www.w3schools.com/sql/sql_func_sum.asp

[Erroneous]

Hi i think i cannot perform a SUM function because it has commas (,)

2,1,0,1,1,5,2 << 1 record in 1 field commas are included.

In my code i split it with comma as the delimiter.

so its like based on my code

myArr(0) = 2
myArr(1) = 1
myArr(2) = 0

and so on. now i need to loop on the recordset and sum it there along the way. i don't know how to loop it where it will put the values on a temp file and movenext to the recordset.

[baja_yu]

Try this

Dim I As Long
Dim J As Long
Dim lngSums(6) As Long
Dim strData() As String

For I = 0 To rs.RecordCount - 1
    strData= Split(rs!FieldFromDbase, ",")

    For J = 0 To 6
        lngSums(J) = lngSums(J) + CLng(strData(J))
    Next J

    rs.Movenext
Next I

I didn't test this, just wrote it here.

[Erroneous]

Thank you for the quick response. I really appreciate it. I will try it and let you know thank you.

[Erroneous]

works great i just modified the code adding " If RS.EOF Then Exit For"

Dim I As Long
Dim J As Long
Dim lngSums(6) As Long
Dim strData() As String

For I = 0 To rs.RecordCount - 1
    If rs.EOF Then Exit For
    strData= Split(rs!FieldFromDbase, ",")

    For J = 0 To 6
        lngSums(J) = lngSums(J) + CLng(strData(J))
    Next J

    rs.Movenext
Next I

Thank you very much. Problem solved.

[baja_yu]

You're welcome!

Please take time to mark the thread Resolved. You can do so by going to the Thread Tools menu, which is above your original post, and choosing Mark Thread Resolved.