Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    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.

    Herewith I had enclosed my Database for your review.

    My Formula will be like this

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

    Thanks and Regards
    R. Vadivelan
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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
    Have a nice day!

  3. #3
    Join Date
    Oct 2013
    Posts
    163
    Thanks Sinndho,

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

    Thanks and Regards
    R. Vadivelan

  4. #4
    Join Date
    Oct 2013
    Posts
    163
    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
    R. Vadivelan

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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(...
    Have a nice day!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Quote Originally Posted by Sinndho View Post
    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
    R. Vadivelan
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  9. #9
    Join Date
    Oct 2013
    Posts
    163
    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
    R. Vadivelan
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In the immediate window (Ctrl+G) and with Form1 open, type:
    Code:
    Forms!Form1!Txt_Sum.Value = "Hello"
    and see what happens.
    Have a nice day!

  11. #11
    Join Date
    Oct 2013
    Posts
    163
    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
    R. Vadivelan

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What do you mean "automatically"?
    Have a nice day!

  13. #13
    Join Date
    Oct 2013
    Posts
    163
    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

    I hope this is the right way, if it is wrong please help me.

    Thanks and Regards
    R. Vadivelan

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Assign the value returned by the function in the Form_Open, Form_Load or Form_Current event of the form.
    Have a nice day!

  15. #15
    Join Date
    Oct 2013
    Posts
    163
    Thanks Sinndho,

    Now It works fine.

    Thanks and Regards
    R. Vadivelan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •