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

This article was previously published under Q197219
This article has been archived. It is offered "as is" and will no longer be updated.
You can use a query to return records that have duplicate values within afield or whose values, between records within that field, are less than aspecified amount. For example, you may want to return a list of all theorders that were placed for the same customer within two days of eachother.The following example looks for multiple orders for the same customerwithin two days of each other. It uses the sample database Northwind.mdb.

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

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

back to the top


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

back to the top

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.
back to the top

Explanation of the Query

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

Because you want to return records for the same customer with differentOrderIDs where the orders are within two days of each other, you need tomake 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 thesame customer, you can use the DateDiff() function. The function in thequery calculates the number of days between the OrderDate from the firstinstance of the table and OrderDate in the second instance of the table.The criterion limits the records to plus or minus two days. The secondtable should translate to "all the other records in the same table." Thereis one catch with this.

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

You may go further and return all the orders made to the same customer bythe same employee within one or two days of each other. To do this, createan additional join between the EmployeeID field from the first table to theEmployeeID 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.

back to the top

Article ID: 197219 - Last Review: 10/26/2013 00:05:00 - Revision: 3.0

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbhowto kbhowtomaster KB197219