Find Average from values in Access Database

[consc197]

I am coding some new forms and i need some help i believe.

Here is what i want to do:

I have a table called tblComparison - this table has the following columns:

• fldDate
• 
  
• fldAverage
• 
  
• fldMorning8am
• 
  
• fldMorning11am
• 
  
• fldLunch2pm
• 
  
• fldAfternoon6pm
• 
  
• fldNight1159pm

The 5 last columns have numbers entered. The first column has a date entered in the form of DD/MM/YYYY.

(1) In the form i will have ListBox, the user will select a Month - basically lets say the user chooses December the choice will be 31/12/2011
(2) I will need to select from the database all of the rows that have fldDate set from 01/12/2011 to 31/12/2011.
(3) I will need to probably enter the values of the rows in a array or something.
(4) For each row the program needs to find the average so it needs to do fldMorning8am + fldMorning11am + fldLunch2pm + fldAfternoon6pm + fldNight1159pm and then divide the answer by 5 and it needs to insert the answer to the relative row in fldAverage Column
(5) For each of the columns (fldMorning8am, fldMorning11am, fldLunch2pm, fldAfternoon6pm, fldNight1159pm) i need to find the average of the rows in that column.

What i mean is that lets say there are 3 rows with the following details:

12122011 7 7 3 7 7 7
13122011 6 7 7 7 1 7
14122011 7 5 7 2 7 8

(the first column is fldDate, the second here is fldAverage and so on)

So fldMorning8am (third column) column has the following values: 7, 6, 7

I want to take those three values, plus them and then divide them by 3 (since in this case its 3 columns) to get an average, so 7+6+7 = 20 then 20/3 = 6.6666666667

Once the averages for each column are created it needs to save them to tblMonthlyComparison - again the column names in this table are the same so in the fldDate column the program needs to enter 31/12/2011. In fldAverage of this table what we do is take the average that we found above for each column then plus them together and divide them by 5.

I appreciate any assistance, as a note this coding will go in a open source program that i am working on.

[consc197]

For number (1) with the ListBox i could also i suppose store in another table the following:

...
November 01/11/2011 30/11/2011
December 01/12/2011 31/12/2011
January 01/1/2012 31/1/2012
(and so on)

Then i populate the ListBox with the Months, then if the user selects December the program goes to the database and retrieves the first day and last day of the month and searchs tblComparison for the values which match that

[consc197]

I am currently working on number (4) as i explained above:

Here is the code up to now:

Private Sub btnCalculateDailyAverage_Click(sender As System.Object, e As System.EventArgs) Handles btnCalculateDailyAverage.Click

        ' SQL statement.
        Dim SQLMorning8am As String = _
            "SELECT fldMorning8am FROM tblComparison"
        Dim SQLMorning11am As String = _
            "SELECT fldMorning11am FROM tblComparison"
        Dim SQLLunch2pm As String = _
            "SELECT fldLunch2pm FROM tblComparison"
        Dim SQLAfternoon6pm As String = _
            "SELECT fldAfternoon6pm FROM tblComparison"
        Dim SQLNight1159pm As String = _
            "SELECT fldNight1159pm FROM tblComparison"

        '
        ' Connect to database and read the query result into arrays
        '

        ' In this example, we use OleDbConnection to connect to MS Access (Jet Engine).
        ' If you are using MS SQL, you can use SqlConnection instead of OleConnection.
        Dim dbconn As System.Data.IDbConnection = _
            New System.Data.OleDb.OleDbConnection( _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\PsychologicalStateTracker.mdb;")

        dbconn.Open()

        ' Set up the SQL statement
        Dim sqlCmdMorning8am As System.Data.IDbCommand = dbconn.CreateCommand()
        sqlCmdMorning8am.CommandText = SQLMorning8am

        Dim sqlCmdMorning11am As System.Data.IDbCommand = dbconn.CreateCommand()
        sqlCmdMorning11am.CommandText = SQLMorning11am

        Dim sqlCmdLunch2pm As System.Data.IDbCommand = dbconn.CreateCommand()
        sqlCmdLunch2pm.CommandText = SQLLunch2pm

        Dim sqlCmdAfternoon6pm As System.Data.IDbCommand = dbconn.CreateCommand()
        sqlCmdAfternoon6pm.CommandText = SQLAfternoon6pm

        Dim sqlCmdNight1159pm As System.Data.IDbCommand = dbconn.CreateCommand()
        sqlCmdNight1159pm.CommandText = SQLNight1159pm

        ' Read the data into the DBTable object
        Dim tableMorning8am As DBTable = New DBTable(sqlCmdMorning8am.ExecuteReader())
        Dim tableMorning11am As DBTable = New DBTable(sqlCmdMorning11am.ExecuteReader())
        Dim tableLunch2pm As DBTable = New DBTable(sqlCmdLunch2pm.ExecuteReader())
        Dim tableAfternoon6pm As DBTable = New DBTable(sqlCmdAfternoon6pm.ExecuteReader())
        Dim tableNight1159pm As DBTable = New DBTable(sqlCmdNight1159pm.ExecuteReader())


        dbconn.Close()

        ' Get the data as arrays
        Dim dblMorning8am() As Double = tableMorning8am.getCol(0)
        Dim dblMorning11am() As Double = tableMorning11am.getCol(0)
        Dim dblLunch2pm() As Double = tableLunch2pm.getCol(0)
        Dim dblAfternoon6pm() As Double = tableAfternoon6pm.getCol(0)
        Dim dblNight1159pm() As Double = tableNight1159pm.getCol(0)

I seem to be stuck in how to add the data in each row together and then insert the answer to the row in fldAverage column.

[consc197]

I think i've coded most of the things, this is what i need help with:

(1) In the form i will have ListBox, the user will select a Month - basically lets say the user chooses December the choice will be 01/12/2011
(2) I will need to select from the database all of the rows that have fldDate set from 01/12/2011 to 31/12/2011.
(3) I will need to probably enter the values of the rows in a array or something.
(5) For each of the columns (fldMorning8am, fldMorning11am, fldLunch2pm, fldAfternoon6pm, fldNight1159pm) i need to find the average of the rows in that column.

[consc197]

For (2) i think the query would look something like the following:

SELECT *
FROM tblComparison 
WHERE fldDate IN (DatesInMonth)

The DatesInMonth would be a array with the Dates in all months.