SQL How to count dupplicates with crit

[Cybernard]

I have a table with the timetable and I need to know how often a subject is taught a week
One field contains the subject code, another the students sets.
Assume a subject code occurs 4 times and there are two students sets, I can deduct that this course takes place twice a week.
I tried to first select the subject code with a SQL like this:
"SELECT DISTINCT t_Code FROM Timetable"
Then the usual While not rs.eof gives me each subject code,
how can I formulate a query that gives me the number of occurrences of the pair code + students sets something like
SELECT COUNT ([t_Students Sets], t_Code) WHERE t_Code='"strCode"'" but I don't know how to use COUNT or COUNT DISTINCT
I am attaching a print screen of the table
Any idea?

[Cybernard]

Well in the meantime I found a solution that works. Not rocket science regarding SQL queries...
I used three queries:
the first to isolate the different subject codes;
another one to count how often a code appears;
and a third one to count how many students sets are involved and that makes it.
Here the code:

Private Sub CalculateCredits(adoABSMACon As ADODB.Connection)
Dim intTotPeriods As Integer
Dim intStudentsSets As Integer
Dim sngCredits As Single
Dim strSQL As String
Dim strCode As String
Dim rs As ADODB.Recordset
Dim rsPeriods As ADODB.Recordset
Dim rsStudentsSets As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Set rsPeriods = New ADODB.Recordset
    Set rsStudentsSets = New ADODB.Recordset
    strSQL = "SELECT DISTINCT t_Code FROM Timetable"
    Set rs = CreateAndOpenRecordset(adoABSMACon, strSQL)
    While Not rs.EOF
        strCode = rs!t_Code
        strSQL = "SELECT * FROM Timetable WHERE t_Code ='" & strCode & "'"
        Set rsPeriods = CreateAndOpenRecordset(adoABSMACon, strSQL)
        intTotPeriods = rsPeriods.RecordCount
        rsPeriods.Close
        strSQL = "SELECT DISTINCT [t_Students Sets] FROM Timetable WHERE t_Code ='" & strCode & "'"
        Set rsStudentsSets = CreateAndOpenRecordset(adoABSMACon, strSQL)
        intStudentsSets = rsStudentsSets.RecordCount
        rsStudentsSets.Close
        sngCredits = intTotPeriods / intStudentsSets / 2
        UpdateCreditsCurriculum strCode, sngCredits, adoABSMACon
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
    Set rsStudentsSets = Nothing
    Set rsPeriods = Nothing
End Sub

If someone knows a better way (more "professional"), as newbee and "hobbyist" I'm keen to learn new things, thanks

[tkorsano]

maybe, you mean:

select t_student_sets, t_code, count(*) from timetable
group by t_student_sets, t_code

also, you might mean


select t_student_sets, t_code, count(1) from timetable
group by t_student_sets, t_code

maybe one of these queries will give you count as a result of grouping those two columns.

i hope this helps,

tonci korsano

[Cybernard]

Thank you for your answer which unfortunately doesn't do the job.
It gives a students set as result, not the number I expected.
I started a small project to test the queries, here with COUNT(1), as I believe COUNT(*) or (1) is only a matter of dealing with null values and efficiency:

Private Sub Command1_Click()
Dim cmd As ADODB.Command
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
On Error GoTo CleanUp
    strPath = App.Path & Chr(92) & "Database\SatitSchoolatAsianU.mdb"
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                strPath & ";Persist Security Info=False"
    If cn.State = adStateOpen Then
        Set cmd = New ADODB.Command
        cmd.CommandType = adCmdText
        strSQL = "SELECT [t_Students Sets], t_Code, COUNT(1) from Timetable GROUP BY [t_Students Sets], t_Code"
        cmd.CommandText = strSQL
        Set cmd.ActiveConnection = cn
        Set rs = cmd.Execute
        If Not rs Is Nothing Then
            MsgBox rs(0) 'gives "Grade 10 Humanities"(without quotes), so not a number...
            MsgBox rs.RecordCount 'gives "-1", without quotes, so gives -1
        End If
    End If
CleanUp:
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cmd = Nothing
    Set cn = Nothing
End Sub