ACC: How to Use Criteria Queries Without Join Lines

Last reviewed: May 14, 1997
Article ID: Q109959
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

Join lines between tables are used to clarify relationships among data in the joined tables and to sort and order the data. However, there are some situations where it is not necessary to have join lines between tables. This article demonstrates a situation where it is not necessary to have a join line between tables.

MORE INFORMATION

NOTE: In Microsoft Access version 7.0, change all date references in the example from 1995 to 1993, and in Microsoft Access versions 2.0 and 1.x, change all date references from 1995 to 1991 to get correct results.

The sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0) contains a table called Orders that has a column called Order Date. To see all the orders placed from January 1, 1995 to February 1, 1995, you would have to use a query because there are no join properties for unequal (greater than or less than) comparisons.

One way to see the orders from July 1, 1995 to August 1, 1995, is to create a table that holds the beginning and ending dates of the period you want to see, and then to create a query comparing the fields in that table against the Order Date column in the Orders table. The following example demonstrates how to do this:

  1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0.)

  2. Create the following new table:

          Table: Date Range
          -----------------------
          Field Name: Start Date
    
             Data Type: Date/Time
          Field Name: End Date
             Data Type: Date/Time
    
    

  3. Enter the following data in the Date Range table:

         Start Date: 01-Jul-95
         End Date: 01-Aug-95
    

  4. Create a new query based on the Orders and Date Range tables.

  5. Drag all the fields from the Orders table to the query grid.

  6. Enter the following in the Criteria row of the OrderDate column:

          >=[Date Range].[Start Date] And <=[Date Range].[End Date]
    

  7. Run the query. Note that the query results in a recordset containing 31 records (or 25 in version 7.0, and 34 in versions 2.0 and 1.x).

There are other ways to accomplish this task. One way is to run a parameter query, in which you are prompted for the beginning and ending dates each time you run the query. The drawback to this method though is that the beginning and ending dates are not saved and must be entered each time you run the query. However, you can work around this drawback by using a form to prompt for the beginning and ending dates and then storing the values in the Date Range table. The next time you open the form, the values last used will be displayed and can be changed if necessary.

REFERENCES

For more information about passing values from a form to a parameter query, search the Help Index for "Query By Form," or ask the Microsoft Access 97 Office Assistant.

For more information about parameter queries, search the Help Index for "parameter queries."


Additional query words: queries
Keywords : DcmNwd kbusage
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 14, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.