Sample Queries

This topic presents sample queries that are typical of the kinds of queries you might need to do in the performance of your responsibilities. These sample queries include:

• A wildcard query
• A range query
• A null query
• A query that includes reference to the current date

Sample Wildcard Query

Suppose you work in Human Resources, and you need to access the employee records for all employees in the Administration department (400) whose last names begin with letters in the last half of the alphabet. Your query might work something like this:

1. Open the Employees form, from which you can access all employee personnel records.
2. On the toolbar, click the Filter button, which retrieves all employee records to this form.
3. Press CTRL+Q to display the Employees Query form.
4. On the Primary Criteria tab:
   • With Last Name as the criteria, select  > (Greater than) as the operator
   • Specify M* in the adjacent value field.
5. On the Additional Criteria tab, specify this information in the three query criteria fields:
   • From the first drop-down list, select Department.
   • From the operator drop-down list, select = (Equals).
   • From the adjacent value drop-down list, select 400, which is the Administration department.
6. Click Add.
7. Click Refresh. The system retrieves all records that meet the specified criteria and displays them in the Results grid.
8. To return these records to the parent multiview form (Employees), click OK.
9. When the system prompts for confirmation, click Yes.

NOTE: When you click Yes, the system returns all retrieved records to the parent form, replacing whatever was previously displayed in that form. You cannot pick and choose which records to return.

Sample Range Query

Suppose you want to find all records for a particular vendor (ID number 9), relating to payments made during the latter half of the month of September, 2011. This means that we want to query for a set of records spanning a calendar range from September 16 to September 30, 2011.

You can use a query like this:

1. Open the A/P Payments form.
2. On the toolbar, click the Filter button, which retrieves all accounts payable (A/P) payment records.
3. Press CTRL+Q.
4. On the A/P Payments Query form, Primary Criteria tab, for the Vendor criteria fields:
   • Select = (Equals) as the operator.
   • In the adjacent drop-down list, select the appropriate vendor ID, in this case, 9.
5. On the Additional Criteria tab, in the three query criteria fields:
   • From the first drop-down list, select Check Date.
   • From the operator drop-down list, select > (Greater than).
   • From the adjacent value drop-down list, enter 09/15/2011.

   Because there is no operator for "greater than or equal to" (>=), we use the day before the first day for which we want records.

6. Click Add.
7. From the first drop-down list, select Check Date.
8. From the operator drop-down list, select < (Less than).
9. From the adjacent value drop-down list, enter 10/01/2011.

   Because there is no operator for "less than or equal to" (<=), we use the day after the last day for which we want records.

10. Click Add.
11. Click Refresh.

    The system retrieves all records that meet the specified criteria and displays them in the Results grid.

12. To return these records to the parent multiview form (A/P Payments), click OK.
13. When the system prompts for confirmation, click Yes.

NOTE: When you click Yes, the system returns all retrieved records to the parent form, replacing whatever was previously displayed in that form. You cannot pick and choose which records to return.

Sample Null Query

Suppose you want to identify all locations for your company that have no work centers associated with them. You can use a query like this:

1. Open the Locations form.
2. Press CTRL+Q.
3. On the Additional Criteria tab, in the three query criteria fields:
   • From the first drop-down list, select WC (for "Work Center").
   • From the operator drop-down list, select = (Equals).
   • In the adjacent value field, enter null.
4. Click Add.
5. Click Refresh.

   The system retrieves all records that meet the specified criteria and displays them in the Results grid.

6. To return these records to the parent multiview form (Locations), click OK.
7. When the system prompts for confirmation, click Yes.

NOTE: When you click Yes, the system returns all retrieved records to the parent form, replacing whatever was previously displayed in that form. You cannot pick and choose which records to return.

Sample Query Using Current Date

Suppose you want to find all jobs with a start date in the past week. From the Job Orders form, use a query that includes the CURDATE keyword, like this:

Start < CURDATE(1) 
AND Start > CURDATE(-8)

CURDATE() with no parameters returns just the date with no time component. CURDATE(30) represents the current date plus 30 days, and CURDATE(-30) represents 30 days in the past.

Related Topics

About Query Forms

Additional Criteria in Query Forms

Buttons on Query Forms

Finding Records Using a Query Form

Operators on Query Forms

Primary Criteria in Query Forms

Understanding Filters

Using Wildcard Characters

Using the Null Keyword