ACC2000: How to Use a Query to Compare Data Among Records (208890)

MORE INFORMATION

The following example shows you how to use a query to compare data among the records in the Orders table in the sample database Northwind.mdb. For example, to find orders that were followed by another order within 60 days, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Microsoft Access, and then open the sample database Northwind.mdb.
2. In the Database window, click Queries under Objects, click Create query in Design view, and then click Design.
3. In the Show Table dialog box, click the Tables tab, select Orders from the list, and then click Add twice. Click Close.
   
   NOTE: The second time that you add the table, Access adds it as Orders_1.
4. Drag CustomerID from the Orders field list to CustomerID in the Orders_1 field list to join the tables in the CustomerID field.
5. Add the following fields to the query grid:

   Field: CustomerID
   Table: Orders
   Sort: Ascending
   
   Field: OrderDate
   Table: Orders
   Sort: Ascending
   
   Field: DaysBetweenOrders: DateDiff("d",[Orders].[OrderDate],[Orders_1].[OrderDate])
   Criteria: Between 1 And 60
   
   Field: OrderID
   Table: Orders
   
   Field: NextOrderDate: [OrderDate]
   Table: Orders_1
   					

6. Run the query.

Note that every order followed by another order within 60 days is returned.