INNER JOIN Operation Example (DAO)

INNER JOIN Operation Example

This example creates two equi-joins: one between the Order Details and Orders tables and another between the Orders and Employees tables. This is necessary because the Employees table does not contain sales data, and the Order Details table does not contain employee data. The query produces a list of employees and their total sales.

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

Sub InnerJoinX()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind

    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    

    ' Create a join between the Order Details and

    ' Orders tables and another between the Orders and

    ' Employees tables. Get a list of employees and

    ' their total sales.

    Set rst = dbs.OpenRecordset("SELECT DISTINCTROW " _

        & "Sum(UnitPrice * Quantity) AS Sales, " _

        & "(FirstName & Chr(32) & LastName) AS Name " _

        & "FROM Employees INNER JOIN(Orders " _

        & "INNER JOIN [Order Details] " _

        & "ON [Order Details].OrderID = " _

        & "Orders.OrderID ) " _

        & "ON Orders.EmployeeID = " _

        & "Employees.EmployeeID " _

        & "GROUP BY (FirstName & Chr(32) & LastName);")

    

    ' Populate the Recordset.

    rst.MoveLast

    

    ' Call EnumFields to print the contents of the

    ' Recordset. Pass the Recordset object and desired

    ' field width.

    EnumFields rst, 20

    dbs.Close

End Sub