ACC: How to Compare Data Among Records in a Query

ID: Q122243

The information in this article applies to:

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."

Additional query words: previous next 


Keywords          : QryHowto 
Version           : 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 8, 1999