Query - Find the total no. of student for a particular course

[Guest_imported]

How do I find the total no. of student for a particular course using SQL statements?
Data and details...

ENTROLL TABLE:
CLASSID(PK) STUID(PK)
ART103A S001
MTH111B S002
ART103A S003

How do I query in order to get an answer of 2 students that have been entrolled in class ART103A?

 

[bryant89]

Try this.. There are no guarantees

Create a form with a button called
In the properties for the button on the events use this code for the onclick event

---------------------------------------------------------
Private Sub btnRunQuery_Click()

Dim strclass AS STRING
Dim dbs As Database, rst As Recordset

' Modify this line to include the path to your database
' on your computer.
Set dbs = OpenDatabase("C:\AccessDB-equinoxserver\Equinoxserver.mdb&quot

' Count the number of records with a PostalCode
' value and return the total in the Tally field.
Set rst = dbs.OpenRecordset("SELECT COUNT(STUID) AS NUMSTUDENTS FROM ENTROLL_TABLE WHERE CLASSID= & strclass;&quot

' Populate the Recordset.
rst.MoveLast

' Call EnumFields to print the contents of

' the Recordset. Specify field width = 12.
EnumFields rst, 12

dbs.Close

End Sub

Sub EnumFields(rst As Recordset, intFldLen As Integer)

Dim lngRecords As Long, lngFields As Long
Dim lngRecCount As Long, lngFldCount As Long
Dim strTitle As String, strTemp As String

' Set the lngRecords variable to the number of
' records in the Recordset.
lngRecords = rst.RecordCount
' Set the lngFields variable to the number of
' fields in the Recordset.
lngFields = rst.Fields.Count

Debug.Print "There are " & lngRecords _
& " records containing " & lngFields _
& " fields in the recordset."
Debug.Print

' Form a string to print the column heading.
strTitle = "Record "
For lngFldCount = 0 To lngFields - 1
strTitle = strTitle _
& Left(rst.Fields(lngFldCount).Name _
& Space(intFldLen), intFldLen)
Next lngFldCount

' Print the column heading.
Debug.Print strTitle
Debug.Print

' Loop through the Recordset; print the record
' number and field values.
rst.MoveFirst
For lngRecCount = 0 To lngRecords - 1

Debug.Print Right(Space(6) & _
Str(lngRecCount), 6) & " ";
For lngFldCount = 0 To lngFields - 1
' Check for Null values.
If IsNull(rst.Fields(lngFldCount)) Then
strTemp = &quot;<null>&quot;
Else
' Set strTemp to the field contents.
Select Case _
rst.Fields(lngFldCount).Type
Case 11
strTemp = &quot;&quot;
Case dbText, dbMemo
strTemp = _
rst.Fields(lngFldCount)

Case Else
strTemp = _
Str(rst.Fields(lngFldCount))
End Select
End If
Debug.Print Left(strTemp _
& Space(intFldLen), intFldLen);
Next lngFldCount
Debug.Print
rst.MoveNext
Next lngRecCount

End Sub

 

[MichaelRed]

Really???

Why not the simple crosstab Query shown below?

TRANSFORM Count(tblEnroll.RecId) AS [The Value]
SELECT tblEnroll.CLASSID, Count(tblEnroll.STUID) AS Total
FROM tblEnroll
GROUP BY tblEnroll.CLASSID
PIVOT tblEnroll.STUID;


CLASSID Total S001 S002 S003
ART103A 2 1 1
MTH111B 1 1

of course, I did take some liberties w/ the table name, but I'm sure these can be modified to suit the situation.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over