Separating Chart Series

I currently have a chart element that has 4 Series. My problem is that while they work individually, if I set more than 1 Series it sets the data for all of them to the last DataSet (So Series 1,2 & 3 have the same positions as 4).

Could someone have a look at my broken code and let me know where it's all going wrong? And maybe some pointers on neatening it up... I have never worked with any form of charts before.

Using con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\Response.mdb;")


    Dim MyQuery As String = "SELECT qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood, Count(qry_Response_By_Date_1.Mood) AS CountOfMood FROM qry_Response_By_Date_1 GROUP BY qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood HAVING (((qry_Response_By_Date_1.Mood)='Happy'));"
    Using cmd = New OleDbCommand(MyQuery, con)
        Dim MyData As New OleDbDataAdapter(MyQuery, con)
        Dim MyDataSet As New DataSet
        con.Open()
        MyData.Fill(MyDataSet, "Table")
        ChrtMoodChanges.DataSource = MyDataSet.Tables("Table")
        Dim Series1 As Series = ChrtMoodChanges.Series("Series1")
        Series1.Name = "Happy"
        ChrtMoodChanges.Series(Series1.Name).XValueMember = "Actual_Date"
        ChrtMoodChanges.Series(Series1.Name).YValueMembers = "CountOfMood"
    End Using


    Dim MyQuery2 As String = "SELECT qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood, Count(qry_Response_By_Date_1.Mood) AS CountOfMood FROM qry_Response_By_Date_1 GROUP BY qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood HAVING (((qry_Response_By_Date_1.Mood)='Neutral'));"
    Using cmd = New OleDbCommand(MyQuery2, con)
        Dim MyData2 As New OleDbDataAdapter(MyQuery2, con)
        Dim MyDataSet2 As New DataSet
        MyData2.Fill(MyDataSet2, "Table")
        ChrtMoodChanges.DataSource = MyDataSet2.Tables("Table")
        Dim Series2 As Series = ChrtMoodChanges.Series("Series2")
        Series2.Name = "Neutral"
        ChrtMoodChanges.Series(Series2.Name).XValueMember = "Actual_Date"
        ChrtMoodChanges.Series(Series2.Name).YValueMembers = "CountOfMood"
    End Using


    Dim MyQuery3 As String = "SELECT qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood, Count(qry_Response_By_Date_1.Mood) AS CountOfMood FROM qry_Response_By_Date_1 GROUP BY qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood HAVING (((qry_Response_By_Date_1.Mood)='Sad'));"
    Using cmd = New OleDbCommand(MyQuery3, con)
        Dim MyData3 As New OleDbDataAdapter(MyQuery3, con)
        Dim MyDataSet3 As New DataSet
        MyData3.Fill(MyDataSet3, "Table")
        ChrtMoodChanges.DataSource = MyDataSet3.Tables("Table")
        Dim Series3 As Series = ChrtMoodChanges.Series("Series3")
        Series3.Name = "Sad"
        ChrtMoodChanges.Series(Series3.Name).XValueMember = "Actual_Date"
        ChrtMoodChanges.Series(Series3.Name).YValueMembers = "CountOfMood"
    End Using


    Dim MyQuery4 As String = "SELECT qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood, Count(qry_Response_By_Date_1.Mood) AS CountOfMood FROM qry_Response_By_Date_1 GROUP BY qry_Response_By_Date_1.Actual_Date, qry_Response_By_Date_1.Mood HAVING (((qry_Response_By_Date_1.Mood)='Angry'));"
    Using cmd = New OleDbCommand(MyQuery4, con)
        Dim MyData4 As New OleDbDataAdapter(MyQuery4, con)
        Dim MyDataSet4 As New DataSet
        MyData4.Fill(MyDataSet4, "Table")
        ChrtMoodChanges.DataSource = MyDataSet4.Tables("Table")
        Dim Series4 As Series = ChrtMoodChanges.Series("Series4")
        Series4.Name = "Angry"
        ChrtMoodChanges.Series(Series4.Name).XValueMember = "Actual_Date"
        ChrtMoodChanges.Series(Series4.Name).YValueMembers = "CountOfMood"
    End Using
End Using

P.S. the SQL returns the correct data for each... it's just getting it to store the correct data in the charts is my problem.

Everytime you set the ChrtMoodChanges.DataSource you are overwriting the datasource for the chart, thats why you end up with only the last dataset.

I would suggest you merge your datatables before you start binding them to the chart then reference each series to a column in your datasource/datatable

ChrtMoodChanges.Series(Series1.Name).XValueMember = "Actual_Date"
ChrtMoodChanges.Series(Series1.Name).YValueMember = "CountOfMood_Col1"
....
....
ChrtMoodChanges.Series(Series4.Name).XValueMember = "Actual_Date"
ChrtMoodChanges.Series(Series4.Name).YValueMember = "CountOfMood_Col4"

So as a suggestion to cleaning up the code, I would do the below