Help with my select statement please,

[Jacksparrow87]

Hi people,

I was hoping someone could offer me some help on my select statement. First I will try to explain how my data is shown:

My table looks like as follows:

LogNumber, NoteNumber, Company, ChargeCode, Date, Time Spent, etc
1,3Adidias,then the other info in all of the rows.
1,2Adidas
1,1Adidas
2,4Nike
2,3Nike
2,2Nike
2,1Nike
3,3Reebok
3,2Reebok
3,1Reebok
4,3Umbro
4,2Umbro
4,1Umrbo

What I need help with is basically on the statistics part of my program, I want the user to choose a date from dtp1 and dtp2 (Date from and Date to), then I want the user to choose a company. Once they have chosen all of the above and clicked on search I want to execute my select statement, however I want the answer counted up for example as follows:

Company, Chargecode, Number of calls with charge codes
Adidas, RSA, 5
Adidas, RST, 6
Adidas, RAG, 9
Adidas, TSA, 10

Now comes the hard part, as the same charge code is assigned to each log (so every same lognumber has the same charge code), I only really want to count the charge code once rather than each time from the same log number. On top of this the date format of my dtp is dd/mm/yyyy however the data shown in the datagrid is like dd/mm/yyyy hh:mm, so I dont know how I would do that.

So far I have the following select statement:

    Private Sub LoadLog()
        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb")
        Dim adapter As New OleDbDataAdapter("Select Log.* from Log, (select lg.LogNumber, Maxlg.NoteNumber) as MaxNN from Log lg group by lg.LogNumber) temp where Log.LogNumber = temp.LogNumber and Log.NoteNumber = temp.MaxNN order by Log.LogNumber ", conn)
        Dim dt As New DataTable("Log")
        adapter.Fill(dt)
        dglog.DataSource() = dt
        dt.DefaultView.AllowNew = False
    End Sub

However all this does is show all the maximum notenumber for everyone.

Any help please?

 

[jebenson]

I think this should do it:

Select Company, ChargeCode, Count(ChargeCode) as NumberOfCalls from Log Group By Company, ChargeCode

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Jacksparrow87]

Thanks ffor your help but a few points:

1, The user selects a particular company from combobox1.
2, The user selects two dates from dtp1 and dtp2 and the results that are displayed must be between these days (however there is only 1 Date field.)
3, Currently the results shows the results like:

Company, Charge Code:

However I would prefer it like:

COmpany, Charge Code, Number of charge codes assigned

Hope that helps.

 

[Jacksparrow87]

So far I have the following:

Dim adapter As New OleDbDataAdapter("Select Log.* from Log, (select lg.LogNumber, Max(lg.NoteNumber) as MaxNN, Company, Chacode, Count(ChaCode) as NumberOfCalls from Log lg group by lg.company, lg.chacode) temp where Log.LogNumber = temp.LogNumber and Log.NoteNumber = temp.MaxNN And Company = “cmbcomp.text” And Dol = between “dtp.text” and “dtp2.text” order by Log.LogNumber ", conn)

 

[Sorwen]

However I would prefer it like:

COmpany, Charge Code, Number of charge codes assigned

What jebenson gave you should have given you that. Then to add the date range all you should have to add is:

HAVING (((Log.Dol) Between " & dtp.text & " And " & dtp2.text & "));"

So the full thing:
Select Company, ChargeCode, Count(ChargeCode) as NumberOfCalls from Log Group By Company, ChargeCode HAVING (((Log.Dol) Between " & dtp.text & " And " & dtp2.text & "));"


-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!