Microsoft KB Archive/209135

= ACC2000: How to Use Select Queries Without Join Lines =

Article ID: 209135

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209135



For a Microsoft Access 97-and-earlier version of this article, see 109959.

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

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
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.



MORE INFORMATION
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.

 Open the sample database Northwind.mdb.  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  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. Create a new query based on the Orders and Date Range tables. Move all the fields from the Orders table to the query grid. Type the following in the Criteria row of the OrderDate column:

>=[StartDate] And <=[EndDate]

</li> Run the query.

Note that the query results in a recordset that contains 34 records.</li></ol>

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.

<div class="references_section">