Microsoft KB Archive/208890

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

Article ID: 208890

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q208890



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

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
This article shows you how you can use a query instead of code to compare data among records.



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.

 Start Microsoft Access, and then open the sample database Northwind.mdb. In the Database window, click Queries under Objects, click Create query in Design view, and then click Design. 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. Drag CustomerID from the Orders field list to CustomerID in the Orders_1 field list to join the tables in the CustomerID field.  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  Run the query.</li></ol>

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

<div class="references_section">