Find what materials were allocated for previous orders--SQL 2008, VS 2015

[kpmc]

So, you are wanting to populate 4 tables from data that you already have from 3 tables? I think there are some design considerations to be made with your solution. You may want to consider using datarelations and column expressions to produce the results you want. Here is a simple example.

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("SomeNumber"), 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
        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