ACC: How to Compare Data Among Records in a Query

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

You usually use code to compare data among records in a table. This article describes how to use a query instead of code to compare data among records.

MORE INFORMATION

The following example demonstrates how to use a query to compare data among the records in the Orders table in the sample database Northwind.mdb (or NWIND.MDB in version 2.0). This query finds orders that were followed by another order within 60 days:

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

2. Create a new query. In the Show Table dialog box( or Add Table dialog box in version 2.0), add the Orders table twice. The second time you add the table, it is added as Orders_1.

3. Join the tables on the CustomerID field (or Customer ID field in version 2.0.)

4. Create the following columns in the query grid.

   NOTE: In the following query, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this query.

   NOTE: In Microsoft Access 2.0, replace all instances of [CustomerID], [OrderID] and [OrderDate] in the following example with [Customer ID], [Order ID] and [Order Date] respectively.

         Query: Compare Orders Query
         ---------------------------
         Type: Select Query
         Join: Orders.[CustomerID]<->Orders_1.[CustomerID]
   

         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
   
   

5. Run the query. Every order followed by another order within 60 days is returned.

REFERENCES

For more information about queries, search the Help Index for "designing queries."