Hi people,

I was hoping someone could help me out please.

What I need is to do is basically run a sql statement where it shows the company name, chare code, counts of all the charge codes and the sum of time spent.

For example I have the following statement so far:

Code:
        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb")
        Dim adapter As New OleDbDataAdapter

        Dim selCommand As New OleDbCommand
        With selCommand
            .CommandText = "Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes],  COUNT(TSpent) As [NumberofTimeSpent] From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode], [TSpent]"
            .CommandType = CommandType.Text
            .Connection = conn
            'Add Parameters 
            .Parameters.Add(New OleDbParameter("@Date1", OleDbType.Date)).Value = dtp.Value
            .Parameters.Add(New OleDbParameter("@Date2", OleDbType.Date)).Value = dtp2.Value
            .Parameters.Add(New OleDbParameter("@CompID", OleDbType.Integer)).Value = cmbcomp.SelectedValue
        End With
        'assign select command to data adapter 
        adapter.SelectCommand = selCommand
        Dim dt As New DataTable("Log")
        adapter.Fill(dt)
        dglog.DataSource() = dt
        dt.DefaultView.AllowNew = False
What the above coding does is list the answer as follows:

Company name,Charge Code, Count, Time Spent
Adidas, ABS, 5, 5
Adidas, ABS, 7, 7
Adidas, RYS. 4, 4

However I want it as follows:

Company name,Charge Code, Count, Time Spent
Adidas, ABS, 12, (Total Sum)
Adidas, RYS. 4, (Total Sum)

Any help please?

**EDIT** Tried the following:

Code:
.CommandText = "Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode]"
Which shows everything correctly (apart from the time spent column) So I decided to include the Time Spent column which I assume will have to be a SUM function as I want to add up all the time spent for that particular charge code, so my statement has been changed to:

Code:
.CommandText = "Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes],  SUM(TSpent) As [NumberofTimeSpent] From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode], [TSpent]"
However this totally shows the wrong data as the Charge Code column is no longer counted but listed per charge code and the time spent is not even showing the correct values.