# Thread: Sumproduct Function in Access

1. Registered User
Join Date
Oct 2013
Posts
165

## Unanswered: Sumproduct Function in Access

Dear Seniors,

With help of this forum, I have created my Document Tracking Database for my project use. Now everything is working fine except the Progress Calculation. I would like to do the progress calculcation in Access.

Progress calculation can be performed with Sumproduct function. Could you please help me to understand how we can perform Sumproduct in Access since there is no built in funtion for the same.

I would like to display the results in a form, when I click the calculate button.

I understood that We can use excel function in access by Application.Worksheet function however I don't know how to get the Query field names in the same.

My Formula will be like this

Sumproduct ([MHRS NEW INT],[InternalProgress])/sum(([MHRS NEW INT])

Thanks and Regards

2. Moderator
Join Date
Mar 2009
Posts
5,442
From http://office.microsoft.com/en-us/ex...05209293.aspx: SUMPRODUCT Multiplies corresponding components in the given arrays, and returns the sum of those products.

We must then create a function that receives two arrays of variable dimensions (different number of lines and/or different number of columns), multiply the value of each row in each column in the first array by the corresponding value in the second array then add the results. If the dimensions of both arrays are different, the extra rows or extra columns will be discarded in the computation. This is rather logical if we consider that the missing row(s) or column(s) contain all zeroes, meaning that their multiplication with any number will yield zero (x * 0 = 0), then won't change the final addition (x + 0 = x).

In Access, it's easy enough to get an array from a table or query: Open a Recordset that selects the appropriate columns, then use the GetRows() method of the Recordset object.

1. Create two samble tables (Table1 and Table2):
Code:
```Sub BuildSampleTables()

Const c_DDL1 As String = "CREATE TABLE [Table1] ( [SysCounter] COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"[NumData1] INTEGER NULL, " & _
"[NumData2] INTEGER NULL, " & _
"[TxtData] TEXT(50) NULL " & _
");"
Const c_DDL2 As String = "CREATE TABLE [Table2] ( [SysCounter] COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"[NumCol1] INTEGER NULL, " & _
"[TxtCol] TEXT(50) NULL, " & _
"[NumCol2] INTEGER NULL " & _
");"
Const c_SQL1 As String = "INSERT INTO Table1 ( NumData1, NumData2, TxtData ) " & _
"SELECT * FROM ( SELECT 3 AS NumData1, 8 AS NumData2, 'three-eight' AS TxtData FROM MSysObjects UNION " & _
"SELECT 8, 2, 'eight-two' FROM MSysObjects UNION " & _
"SELECT 1, 4, 'one-four' FROM MSysObjects UNION " & _
"SELECT 5, 5, 'five-five' FROM MSysObjects " & _
");"
Const c_SQL2 As String = "INSERT INTO Table2 ( NumCol1, TxtCol, NumCol2) " & _
"SELECT * FROM ( SELECT 4 AS NumCol1, 'four-six' AS TxtCol, 6 AS NumCol2 FROM MSysObjects UNION " & _
"SELECT 6, 'six-three', 3 FROM MSysObjects UNION " & _
"SELECT 9, 'nine-nine', 9 FROM MSysObjects UNION " & _
"SELECT 2, 'two-seven', 7 FROM MSysObjects UNION " & _
"SELECT 7, 'seven-one', 1 FROM MSysObjects " & _
");"
CurrentDb.Execute c_DDL1, dbFailOnError
CurrentDb.Execute c_SQL1, dbFailOnError
CurrentDb.Execute c_DDL2, dbFailOnError
CurrentDb.Execute c_SQL2, dbFailOnError

End Sub```
2. The SumProduct function:
Code:
```Public Function SumProduct(Array1 As Variant, Array2 As Variant) As Variant

Dim lngRowMax As Long
Dim lngColMax As Long
Dim i As Long
Dim j As Long
Dim var As Variant

' Compute the littlest upper dimensions (MaxRow, MaxCol) of both arrays
'
lngRowMax = IIf(UBound(Array1, 2) <= UBound(Array2, 2), UBound(Array1, 2), UBound(Array2, 2))
lngColMax = IIf(UBound(Array1) <= UBound(Array2), UBound(Array1), UBound(Array2))
ReDim var(0 To lngRowMax)
For i = 0 To lngRowMax
For j = 0 To lngColMax
var(i) = var(i) + Array1(j, i) * Array2(j, i)
Next j
SumProduct = SumProduct + var(i)
Next i

End Function```
3. Test the function:
Code:
```Function TestSumProduct()

Dim rst As DAO.Recordset
Dim var1 As Variant
Dim var2 As Variant

Set rst = CurrentDb.OpenRecordset("SELECT NumData1, NumData2 FROM Table1;")
var1 = rst.GetRows(DCount("*", "Table1"))
rst.Close
Set rst = CurrentDb.OpenRecordset("SELECT NumCol1, NumCol2 FROM Table2;")
var2 = rst.GetRows(DCount("*", "Table2"))
rst.Close
Set rst = Nothing
MsgBox "SumProduct = " & SumProduct(var1, var2), vbInformation, "TestSumProduct"

End Function```

3. Registered User
Join Date
Oct 2013
Posts
165
Thanks Sinndho,

I will make a try with your coding today and let me ask you for any clarifications.

Thanks and Regards

4. Registered User
Join Date
Oct 2013
Posts
165
Hi Sinndho,

I had tried your code and it works perfect. However the result is displayed in the Message Box. Instead of that I would like to display the results in a Form Text Box. Kindly help me to understand how to do the same.

Thanks and Regards

5. Moderator
Join Date
Mar 2009
Posts
5,442
Assign the value returned by the function to the Value property of a textbox control instead of calling the Message box:
Code:
`SomeTextBoxName.Value = SumProduct(...`

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
so set a return value as part of the function

assign the value to a variable of the same name as the function
you can do that as the last line, or do that as part of the funstion itself

eg

Code:
``` public function Square(value1 as double, value2 as double) as double
square=value1*value2
end function```
Code:
``` public function Square(value1 as double, value2 as double) as double
dim product as double
product=value1*value2
square=product
end function```

7. Registered User
Join Date
Oct 2013
Posts
165
Hi Sinndho,

Originally Posted by Sinndho
Assign the value returned by the function to the Value property of a textbox control instead of calling the Message box:
Code:
`SomeTextBoxName.Value = SumProduct(...`
Code:
```Function TestSumProduct()

Dim rst As DAO.Recordset
Dim var1 As Variant
Dim var2 As Variant

Set rst = CurrentDb.OpenRecordset("SELECT NumData1, NumData2 FROM Table1;")
var1 = rst.GetRows(DCount("*", "Table1"))
rst.Close
Set rst = CurrentDb.OpenRecordset("SELECT NumCol1, NumCol2 FROM Table2;")
var2 = rst.GetRows(DCount("*", "Table2"))
rst.Close
Set rst = Nothing
Form1!Sum.Value = " & SumProduct(var1, var2)"End Function```
I had modified the Code, But the It returns error value as per the attached Screen.

Kindly help me to sort out this issue.

Thanks and Regards

8. Moderator
Join Date
Mar 2009
Posts
5,442
1.
Code:
` Form1!Sum.Value = " & SumProduct(var1, var2)"End Function`
should be:
Code:
``` Forms!Form1!Sum.Value =  SumProduct(var1, var2)
End Function```
or, if the code is run from the class module of Form1:
Code:
```Me.Sum.Value = SumProduct(var1, var2)
End Function```
2. Naming a control "Sum" is not the best of ideas because Sum is a reserved word. Change it to Txt_Sum for instance.

9. Registered User
Join Date
Oct 2013
Posts
165
Hi Sinndho,

I had changed the code as per your comment and renamed "Sum" as "Txt_Sum" but still I am getting the Run time error as per the attached Screen Shot.

Code:
```Function TestSumProduct()

Dim rst As DAO.Recordset
Dim var1 As Variant
Dim var2 As Variant

Set rst = CurrentDb.OpenRecordset("SELECT NumData1, NumData2 FROM Table1;")
var1 = rst.GetRows(DCount("*", "Table1"))
rst.Close
Set rst = CurrentDb.OpenRecordset("SELECT NumCol1, NumCol2 FROM Table2;")
var2 = rst.GetRows(DCount("*", "Table2"))
rst.Close
Set rst = Nothing
Forms!Form1!Txt_Sum.Value = SumProduct(var1, var2)
End Function```
I am getting the error at Highlighted Line.

Kindly help me to sort out this issue.

Thanks and Regards

10. Moderator
Join Date
Mar 2009
Posts
5,442
In the immediate window (Ctrl+G) and with Form1 open, type:
Code:
`Forms!Form1!Txt_Sum.Value = "Hello"`
and see what happens.

11. Registered User
Join Date
Oct 2013
Posts
165
Thanks Sinndho.

It works perfect and got the results. But when I close the database and open it again the result is not displaying. When I run the function in module then I am getting the results.

How to do it automatically.

Thanks and Regards

12. Moderator
Join Date
Mar 2009
Posts
5,442
What do you mean "automatically"?

13. Registered User
Join Date
Oct 2013
Posts
165
Hi Sinndho,

When I closed and open the database, then the form didn't display any value. I would like to display the value in form Whenever I opened it (infact I kept this form as opening page) so that I can see the results on opening my database.

Now I had done the same with coding.

(i.e) call TestSumproduct on form activation

Thanks and Regards

14. Moderator
Join Date
Mar 2009
Posts
5,442
Assign the value returned by the function in the Form_Open, Form_Load or Form_Current event of the form.

15. Registered User
Join Date
Oct 2013
Posts
165
Thanks Sinndho,

Now It works fine.

Thanks and Regards