Specify search conditions (ADP)

You can specify the data rows that appear in your query by specifying search conditions. For example, if you are querying an employee table, you can specify that you want to find only the employees who work in a particular region.

You specify search conditions using an expression. Most commonly the expression consists of an operator and a search value. For example, to find employees in a particular sales region, you might specify the following search criterion for the region column:

='UK'
				

Note   If you are working with multiple tables, the Query Designer examines each search condition to determine whether the comparison you are making results in a join. If so, the Query Designer automatically converts the search condition into a join.

1. If you have not done so already, add the columns or expressions that you want to use within your search condition to the Grid pane

   If you are creating a Select query and do not want the search columns or expressions to appear in the query output, clear the Output column for each search column or expression to remove them as output columns.

2. Locate the row containing the data column or expression to search, and then in the Criteria grid column, enter a search condition.

   Note   If you do not enter an operator, the Query Designer automatically inserts the equality operator "=".

The Query Designer updates the SQL statement in the SQL pane by adding or modifying the WHERE clause.

To link conditions with OR and give them precedence over conditions linked with AND, you must repeat the AND condition for each OR condition.

For example, imagine that you want to find all employees who have been with the company more than five years and have lower-level jobs or are retired. This query requires three conditions, a single condition linked to two additional conditions with AND:

• Employees with a hire date earlier than five years ago, and
• Employees with a job level of 100 or whose status is "R" (for retired).

1. In the Database window, click Queries under Objects, click the query you want to open, and then click Design on the Database window toolbar.
2. In the Grid pane, add the data columns you want to search. If you want to search the same column using two or more conditions linked with AND, you must add the data column name to the grid once for each value you want to search.
3. Create the conditions to be linked with OR by entering the first one into the Criteria grid column and the second (and subsequent ones) into separate Or … columns. For example, to link conditions with OR that search the job_lvl and status columns, enter values as shown here:

   

   Entering the values shown in the grid above produces the following WHERE clause in the statement in the SQL pane:

   WHERE (job_lvl = 100) OR (status = 'R')
   						

4. Create the AND condition by entering it once for each OR condition. Place each entry in the same grid column as the OR condition it corresponds to. For example, to add an AND condition that searches the hire_date column and applies to both OR conditions, enter values as shown here:

   

   Entering the values shown in the grid above produces the following WHERE clause in the statement in the SQL pane:

   WHERE (job_lvl = 100) AND 
     (hire_date < '01/01/91' ) OR
     (status = 'R') AND 
     (hire_date < '01/01/91' )
   						

   Tip

   You can repeat an AND condition by adding it once, and then using the Cut and Paste commands from the Edit menu to repeat it for other OR conditions.

The WHERE clause created by the Query Designer is equivalent to the following WHERE clause, which uses parentheses to specify the precedence of OR over AND:

WHERE (job_lvl = 100 OR status = 'R') AND
   (hire_date < '01/01/91')
				

Note   If you enter the search conditions in the format shown immediately above in the SQL pane, but then make a change to the query in the Diagram or Grid panes, the Query Designer recreates the SQL statement to match the form with the AND condition explicitly distributed to both OR conditions.

To combine conditions with AND, you put the conditions in the same column of the Grid pane. To combine conditions with OR, you put the first one in the Criteria column and additional conditions into an Or … column.

For example, imagine that you want to find either employees who have been with the company for more than five years in lower-level jobs or employees with middle-level jobs regardless of their hire date. This query requires three conditions, two of them linked with AND:

• Employees with a hire date earlier than five years ago and with a job level of 100

  -or-

• Employees with a job level of 200

1. In the Database window, click Queries under Objects, click the query you want to open, and then click Design on the Database window toolbar.
2. In the Grid pane, add the data columns you want to search. If you want to search the same column using two or more conditions linked with AND, you must add the data column name to the grid once for each value you want to search.
3. In the Criteria column, enter all the conditions that you want to link with AND. For example, to link conditions with AND that search the hire_date and job_lvl columns, enter values as shown here:

   

   These grid entries produce the following WHERE clause in the statement in the SQL pane:

   WHERE (hire_date < '01/01/91') AND
     (job_lvl = 100)
   						

4. In the Or … grid column, enter conditions that you want to link with OR. For example, to add a condition that searches for another value in the job_lvl column, enter an additional value as shown here:

   

   Adding a value in the Or … column adds another condition to the WHERE clause in the statement in the SQL pane:

   WHERE (hire_date < '01/01/91' ) AND
     (job_lvl = 100) OR 
    (job_lvl = 200)
   						

You can limit the groups that appear in a query by specifying a condition that applies to groups as a whole— a HAVING clause. After the data has been grouped and aggregated, the conditions in the HAVING clause are applied. Only the groups that meet the conditions appear in the query.

For example, you might want to see the average price of all books for each publisher in a titles table, but only if the average price exceeds $10.00. In that case, you could specify a HAVING clause with a condition such as AVG(price) > 10.

Note   In some instances, you might want to exclude individual rows from groups before applying a condition to groups as a whole.

You can create complex conditions for a HAVING clause by using AND and OR to link conditions.

1. Specify the groups for your query.
2. If it is not already in the Grid pane, add the column on which you want to base the condition. (Most often the condition involves a column that is already a group or summary column.) You cannot use a column that is not part of an aggregate function or of the GROUP BY clause.
3. In the Criteria column, specify the condition to apply to the group.

   The Query Designer automatically creates a HAVING clause in the statement in the SQL pane, such as in the following example:

   SELECT pub_id, AVG(price)
   FROM titles
   GROUP BY pub_id
   HAVING (AVG(price) > 10)
   						

4. Repeat steps 2 and 3 for each additional condition you want to specify.