GROUP BY Clause Example (DAO)

GROUP BY Clause Example

This example creates a list of unique job titles and the number of employees with each title.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub GroupByX1()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind

    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    ' For each title, count the number of employees

    ' with that title.

    Set rst = dbs.OpenRecordset("SELECT Title, " _

        & "Count([Title]) AS Tally " _

        & "FROM Employees GROUP BY Title;")

    ' Populate the Recordset.

    rst.MoveLast

    ' Call EnumFields to print the contents of the

    ' Recordset. Pass the Recordset object and desired

    ' field width.

    EnumFields rst, 25

    dbs.Close

End Sub

For each unique job title, this example calculates the number of employees in Washington who have that title.

Sub GroupByX2()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind

    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    ' For each title, count the number of employees

    ' with that title. Only include employees in the

    ' Washington region.

    Set rst = dbs.OpenRecordset("SELECT Title, " _

        & "Count(Title) AS Tally " _

        & "FROM Employees WHERE Region = 'WA' " _

        & "GROUP BY Title;")

    ' Populate the Recordset.

    rst.MoveLast

    ' Call EnumFields to print the contents of the

    ' Recordset. Pass the Recordset object and desired

    ' field width.

    EnumFields rst, 25

    dbs.Close

End Sub