ACC2000: How to Use Select Queries Without Join Lines

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

The sample database Northwind.mdb contains a table called Orders, which has a column called Order Date. To see all the orders placed from July 1, 1999 to August 1, 1999, 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, 1999 to August 1, 1999, is to create a table that holds the beginning and ending dates of the period that you want to see, and then to create a query that compares the fields in that table against the Order Date column in the Orders table. To do this, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Open the sample database Northwind.mdb.
2. Create the following table in Design view, and then save it as Data Range:

      Field Name: StartDate
               Data Type: Date/Time
   
      Field Name: EndDate
               Data Type: Date/Time
   					

3. Type the following data in the Date Range table:
   StartDate: 01-Jul-97
   EndDate: 01-Aug-97
   NOTE: It is possible that your date format may be different from the example shown.
4. Create a new query based on the Orders and Date Range tables.
5. Move all the fields from the Orders table to the query grid.
6. Type the following in the Criteria row of the OrderDate column:
   >=[StartDate] And <=[EndDate]
7. Run the query.
   
   Note that the query results in a recordset that contains 34 records.

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 that you run the query. The drawback to this method is that the beginning and ending dates are not saved and must be entered each time that 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 that you open the form, the values last used will be displayed and can be changed if necessary.

For more information about parameter queries and passing values from a form to a parameter query, click Microsoft Access Help on the Help menu, type parameter query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.