Microsoft KB Archive/122243

= ACC: How to Use a Query to Compare Data Among Records =

Article ID: 122243

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q122243



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



SUMMARY
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:

 Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0). 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. Join the tables on the CustomerID field (or Customer ID field in version 2.0.)  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  Run the query. Every order followed by another order within 60 days is returned.</ol>

<div class="references_section">