ACC: How to Use Criteria Queries Without Join Lines
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:
- Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x
and 2.0.)
- Create the following new table:
Table: Date Range
-----------------------
Field Name: Start Date
Data Type: Date/Time
Field Name: End Date
Data Type: Date/Time
- Enter the following data in the Date Range table:
Start Date: 01-Jul-95
End Date: 01-Aug-95
- Create a new query based on the Orders and Date Range tables.
- Drag all the fields from the Orders table to the query grid.
- Enter the following in the Criteria row of the OrderDate column:
>=[Date Range].[Start Date] And <=[Date Range].[End Date]
- 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 : QryJoin
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbhowto