none
SUMIFS Function in Visual Basic RRS feed

  • Question

  • Hi everyone, I want to ask

    Im using Microsoft Visual Studio 2013 and Mysql for the database.

    I have 2 datatable :

    1. customer_data , the column is id, name, curr(USD), curr(IDR), curr(SGD)

    2. deposit_data, the column is id, currency, amount

    How can I make customer_data table will count summary of amount in table deposit_data based on id and currency like sumifs function in Excel ?

    Please help, sorry for my bad english.



    • Edited by ryco.huang Tuesday, August 14, 2018 4:58 PM it SUMIFS not SUMIF
    Tuesday, August 14, 2018 1:15 AM

All replies

  • Add your datatables to a dataset. Create a datarelation on the columns you've described. after the relation is setup, manually add a column to the customer table, and refer to documentation concerning column expression, specifically aggregate functions. Look about 1/2 down this article for Parent/Child Relation referencing, also have a look at the "IIF" function. I am sure if you wait around long enough someone will provide you a copy/paste solution.

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi


    • Edited by Gtripodi Tuesday, August 14, 2018 2:40 AM
    Tuesday, August 14, 2018 1:41 AM
  • Hi,

    What is the connection between the two tables? I can write a Function similar to SUMIF in the table deposit_data, but it is not clear how the table customer_data is handled.

    What do you want to do in the table customer_data?

    code:

    Imports System.Data.SqlClient
    Public Class Form1
        Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
        Dim conn As SqlConnection
        Dim sda As SqlDataAdapter
        Dim dt As DataTable
        '
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim t As New List(Of Integer)
            t.Add(1)
            t.Add(5)
            countall(t, "a", "amount")
        End Sub
        Public Function countall(ByVal id As List(Of Integer), ByVal Citeria As String, ByVal ColumnName As String) As Integer
            Dim j As Integer
            Using conn = New SqlConnection(constr)
                conn.Open()
                sda = New SqlDataAdapter("Select * From deposit_data", conn)
                dt = New DataTable()
                sda.Fill(dt)
            End Using
            For i = id(0) - 1 To id(1) - 1
                MsgBox(dt.Rows(i)("currency").ToString)
                If dt.Rows(i)("currency").ToString.Trim = "a" Then
                    j += dt.Rows(i)(ColumnName)
                End If
            Next
            MessageBox.Show(j)
            Return 0
        End Function
    End Class
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 14, 2018 9:18 AM
  • thankyou for your reference sirr
    Tuesday, August 14, 2018 11:17 AM
  • first I want to say that Im beginner in Visual basic

    I just learning about dataset (data relation) from previous comment in this thread.

    What I want to do is balance column(USD, IDR, SGD) in customer_data can do 'autosum' like sumif (in excel) from deposit_data based on id and currency.

    I confused how to describe it in english, so I decide to do some ilustrate with image : https://postimg*cc/image/tl30fw2tz/

    replace * with .


    Tuesday, August 14, 2018 11:45 AM
  • first I want to say that Im beginner in Visual basic

    I just learning about dataset (data relation) from previous comment in this thread.

    What I want to do is balance column(USD, IDR, SGD) in customer_data can do 'autosum' like sumif (in excel) from deposit_data based on id and currency.

    I confused how to describe it in english, so I decide to do some ilustrate with image : https://postimg*cc/image/tl30fw2tz/

    replace * with .


    Can you provide an example of the formula you would use in excel? I assume if the currency type is > 0 that is the value you want to SUM? I would probably have just one column for value and an additional column for currency type.

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Tuesday, August 14, 2018 12:51 PM
  • Here is the example on excel :

    https://postimg*cc/image/wr1ebea6v/

    Tuesday, August 14, 2018 1:36 PM
  • Here is the example on excel :

    https://postimg*cc/image/wr1ebea6v/

    Few things

    People probably wont want to click your links when you post them. I actually followed them VIA a disposable virtual machine as my trust level is non existent. You can insert images to this forum, though there maybe a restriction if youre new.

    I am more confused now that I see you image.

    Here is an example of a datarelation, it should get you started. Refer to this and the link I posted earlier, you maybe able to work something out with the IIF function within the aggregated samples below.

    Public Class RelationExample
        Dim DS As New DataSet
        Dim ParentBS, ChildBS As New BindingSource
    
        Private Sub RelationExample_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With DS
                .Tables.Add(New DataTable With {.TableName = "ParentTable"})
                .Tables.Add(New DataTable With {.TableName = "ChildTable"})
    
                ''example data
                ''Parent DATA
                With .Tables("ParentTable")
                    .Columns.Add("ParentID", GetType(System.String))
                    For i = 0 To 5
                        .Rows.Add(i.ToString)
                    Next
                End With
    
                ''Child DATA
                With .Tables("ChildTable")
                    .Columns.Add("ParentID", GetType(System.String))
                    .Columns.Add("SomeNumber", GetType(Double))
                    For Each Drow As DataRow In DS.Tables("ParentTable").Rows
                        For i = 0 To 20
                            .Rows.Add(Drow("ParentID"), Int(Rnd() * 5) + 1)
                        Next
                    Next
                End With
            End With
    
            ''Create relations
            Dim ParentCol As DataColumn = DS.Tables("ParentTable").Columns("ParentID")
            Dim ChildCol As DataColumn = DS.Tables("ChildTable").Columns("ParentID")
    
            DS.Relations.Add("MyRelation", ParentCol, ChildCol, False)
            ParentBS.DataSource = DS.Tables("ParentTable")
    
            ''Add columns to parent table for aggregates (here you could maybe use IIF functions for SUM)
            With DS.Tables("ParentTable")
                .Columns.Add("SumSomeNumber", GetType(Double), "Sum(Child.SomeNumber)")
                .Columns.Add("CountSomeNumber", GetType(Double), "Count(Child.SomeNumber)")
                .Columns.Add("AvgSomeNumber", GetType(Double), "Avg(Child.SomeNumber)")
                .Columns.Add("MinSomeNumber", GetType(Double), "Min(Child.SomeNumber)")
                .Columns.Add("MaxSomeNumber", GetType(Double), "Max(Child.SomeNumber)")
            End With
    
            DataGridView1.DataSource = ParentBS
    
            With ChildBS
                .DataSource = ParentBS
                .DataMember = "MyRelation"
            End With
    
            DataGridView2.DataSource = ChildBS
    
            ''Example of getting rows from children on first parent row
            Dim ChildRows() As DataRow = DS.Tables("ParentTable").Rows(0).GetChildRows("MyRelation")
        End Sub
    End Class


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Tuesday, August 14, 2018 5:32 PM