Help and Support
 

powered byLive Search

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

Retired KB ArticleThis 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.
View products that this article applies to.
Article ID:122243
Last Review:January 19, 2007
Revision:2.1
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.

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

Article Translations

 

Other Support Options

  • Need More Help?
    Contact a Support professional by E-mail, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.