HOW TO: Query Records Within Range of Each Other in Access 2000

You can use a query to return records that have duplicate values within a field or whose values, between records within that field, are less than a specified amount. For example, you may want to return a list of all the orders that were placed for the same customer within two days of each other. The following example looks for multiple orders for the same customer within two days of each other. It uses the sample database Northwind.mdb.

In order to compare a field with itself across multiple records, you need to include the table more than once in the query. This way, you can compare a field from the first instance of the table to the same field in the second instance of that same table.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Requirements

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

How to Create the Query Described in the "Summary" Section

1. Create the following query and include the Orders table twice:

      Query: Catalog
      ---------------------------------------------------------------
      Type: Select Query
      Join: Orders.[CustomerID] <-> Orders_1.[CustomerID]
   
      Field: Orders.*
         Table: Orders
      Field: DateDiff("d",[Orders].[OrderDate],[Orders_1].[OrderDate])
         Criteria: Between -2 And 2
      Field: OrderID
         Table: Orders
         Criteria: <> [Orders_1].[OrderID]
   					

2. To test this query, click Run on the Query menu.

Explanation of the Query

The query includes the Orders table twice so that you can compare fields to themselves but in different records.

Because you want to return records for the same customer with different OrderIDs where the orders are within two days of each other, you need to make three comparisons.

The first comparison is to keep the CustomerIDs the same. To do this, simply join the CustomerID field from the first instance of the table (Orders) to CustomerID in the second instance of the table (Orders_1.)

The second comparison is trickier. To compare days between orders for the same customer, you can use the DateDiff() function. The function in the query calculates the number of days between the OrderDate from the first instance of the table and OrderDate in the second instance of the table. The criterion limits the records to plus or minus two days. The second table should translate to "all the other records in the same table." There is one catch with this.

The third comparison is to handle that catch. The query so far will return all the records that have OrderDate within two days of each other for the same customer. However, that will include orders that have the same OrderID. In other words, the second table not only contains all other records; it contains the same record itself. Therefore, you need to limit the records to the ones that have different OrderIDs. That is why you should include the OrderID field from the Orders table and apply the criteria "<>[Orders_1].[OrderID]" (without the quotation marks).

You may go further and return all the orders made to the same customer by the same employee within one or two days of each other. To do this, create an additional join between the EmployeeID field from the first table to the EmployeeID field in the second table.

NOTE: If you are only searching for duplicate records, you may want to use the Microsoft Access Find Duplicates Wizard. To do so, on the Queries tab, click New. When you click Query Wizards, you will see the Find Duplicates Wizard.