Sum Array from Database

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:

Code:

---

    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.

Code:

---

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?

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

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.

Try this

Code:

---

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.

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

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

Code:

---

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.

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.