ALL, DISTINCT, DISTINCTROW, TOP Predicates Example (DAO)

ALL, DISTINCT, DISTINCTROW, TOP Predicates Example

This example creates a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. Using DISTINCTROW produces a list of companies that have at least one order but without any details about those orders.

Sub AllDistinctX()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind

    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    

    ' Join the Customers and Orders tables on the

    ' CustomerID field. Select a list of companies

    ' that have at least one order.

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

        & "CompanyName FROM Customers " _

        & "INNER JOIN Orders " _

        & "ON Customers.CustomerID = " _

        & "Orders.CustomerID " _

        & "ORDER BY CompanyName;")

    

    ' 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