Article ID: 122243 - Last Review: January 19, 2007 - Revision: 2.1

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

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
This article was previously published under Q122243
Moderate: Requires basic macro, coding, and interoperability skills.
Expand all | Collapse all

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

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

REFERENCES

For more information about queries, search the Help Index for "designing queries."
Back to the top
APPLIES TO
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
Back to the top
Keywords: 
kbhowto KB122243
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations