# Introduction

# 

# The purpose of this document is to help you understand what is a query, the value of a query and how to write a query. It also provides you with instructions on how to write several Verification Edit Profiles and how to apply and enter them into the Quality Analysis (verification) Tool. 

# 

# There are 3 Sections to this document.

# 

# Section 1: Understand what a query is and how to write a query.

# Section 2: Learn how to write Verification Edit Profiles using queries.

Section 3: Enter and apply your verification Edit Profiles into the
Quality Analysis (verification) Tool.

# Section 1: Understand what a query is and how to write a query.

# 

# What is a Query?

A query describes records meeting certain criteria that you specify.
Think of a query as a lid on a box with geometric-shaped holes. Only
objects that have the correct shape can be placed in the box; others do
not fit. In this case the query (the lid) has been defined to select
circles and stars, and to exclude all other objects. A query is a
request for certain records.

![](media/image1.png)

**Why Create a Query?**

Understanding how to create and use queries is central to making
effective use of the Quality Analysis Tool software. It helps you narrow
the scope of what you will look at and makes analysis manageable.

Use a query any time you want to work with a subset of all the records
in your database. For instance, you may want to select all records with
a Parental AGI greater than \$30,000.

Queries are used in most functions of the Quality Analysis Tool to
perform specific functions, such as printing, importing, etc. Generally,
any time working with a subset of records makes sense; the Quality
Analysis Tool software provides the option to use a query. Queries are
available in any dialog box where you see the "Selection Criteria"
button.

Except for the Record Layout Report, queries may be used in combination
with the standard reports available in the Quality Analysis Tool
software.

-   **A query will select the students you want to study. The report you
    choose to use, in combination with the query, will give you the
    information you want to see for the students selected in your
    query.**

How to build queries? First, let's look at some definitions and rules.

**Key Terms**

# Query

A request to retrieve data from the Quality Analysis Tool using criteria
that you

#  specify.

**(SELECTED FOR VERIFICATION = Y)**

**This query finds all records with the CPS Verification set to Yes.**

# Operator

> A component of a query statement. Describes the relationship between a
> data field and its specified value. Operators used in the Quality
> Analysis Tool software are equal to (=), greater than (\>), greater
> than or equal to (\>=), less than (\<), less than or equal to (\<=)
> not equal to (\<\>), Like, and Not Like.
>
> **EFC Difference \<\> 0**
>
> **This query finds all records where the EFC Changed.**

# And

> Used to connect two segments of a query. Both segments must be true
> for a record to be selected.

**(DEPENDENCY STATUS = I) AND (EFC = 0)**

**This query finds all records where the student is Independent and has
an EFC of 0.**

# Or

Used to connect two segments of a query. Only one of the segments must
be true for a

record to be selected.

**(DEPENDENCY STATUS = I) OR (EFC = 0)**

**This query finds all records independent student records and all
records where EFC is 0.**

# Like

> An operator that is used in a query to find values in a field that
> match a specified pattern, or "character string."

**(Student's U.S. Income Tax Paid = 0) AND (Transaction Like "IN")**

**This query finds all records where student's taxes paid equals 0 on
the initial ISIR transaction.**

# Parentheses

> Sets apart two or more statements within a complex query and define
> the order in which the data fields are processed.

**(Student's Total Worksheet A \> 1000 AND Parent's Number in College =
2) AND EFC \< 3000**

**This query finds all records where Student's Worksheet A is less than
1000 and Parent's Number in College equals 2. THIS RECORDS ARE FOUND
FIRST BECAUSE OF THE PARENTHESES. Then the RESULT of this parenthetical
statement is processed with EFC \<3000.**

# Parameter Query

#####  A type of query that has fixed fields but not fixed values. When you create a 

parameter query, you specify the field name and operator for the query.
Instead of

specifying a value, you'll use the Prompt at Execution option. This
option tells the

Quality Analysis Tool to prompt you for the field values at the time you
use the query.

**Parent's Number of Family Members \> (\[Parameter\])**

**Before this query will run, you will be asked to specify a number for
the Parent's Number of Family Members**. **PARAMETER means -- YOU
SPECIFY**

# User-defined Field

Special fields that you create for your own use. User-defined fields can
be used in

all modules for building queries.

**SAP = 2**

**This query finds all records where SAP user-defined filed is 2, e.g.
"C" average.**

###### Rules

1.  Queries are processed from left to right, or beginning to end;
    however, **AND** is applied first, even if it appears after **OR**
    in the query statement.

2.  **Parentheses** change the order of precedence. Expressions enclosed
    in parentheses are processed before AND and OR. In the case of
    nested expressions, innermost parentheses are processed first.

3.  Both segments of the query connected by **AND** must be true for a
    field value to be selected.

4.  Only one of the segments connected by **OR** must be true for a
    field value to be selected.

## **Components of a Simple Query Statement**

A query statement has three components:

1.  Data Field ![](media/image2.png)

2.  Operator

3.  Value

**Data Field** includes all available data fields in the Quality
Analysis Tool software and any user-defined field. When you build a
query, the fields that are available will be presented in alphabetical
order in a drop down list.

User-defined fields can also be used in a query. These are special
fields that you create for your use.

**Operator** describes the relationship of a data field to its value:

= Equal to \<\> Not equal to

\< Less than \> Greater than

\<= Less than or equal to \>= Greater than or equal to

Like Similar to Not Like Different from

-   **Note that whenever you use Transaction in a query, you need to use
    the Like and Not Like Operators. Also, be careful when using the
    \<\> (not equal to) operator because it will not recognize (Blank)
    values.**

Use an operator to describe the relationship of the data field to a
specified value. For example:

**Parent's** **Adjusted Gross Income** **=\>** **\$30,000**

**Value** specifies criteria for selection. Specifying the value is the
third step in building a query. The value must be valid. You have two
options when specifying a value for your query:

You can provide a value at the time you create the query. This value
will be a constant; it will automatically be used each time the query is
used.

You can choose a value each time you run a query. Leave the value field
blank at the time you create the query and mark the "Prompt at
Execution" box. This type of query is often known as a parameter query.
Parameter queries are convenient if you query on the same fields
regularly but want to use different values. This query finds all records
with Parents' Adjusted Gross Income of less than \$50,000 and prompts
you to enter the value for Student's Adjusted Gross Income at the time
you run the query. The query finds only the records that meet both
criteria.

**(PARENT'S ADJUSTED GROSS INCOME \< 50,000) AND (STUDENT'S**

**ADJUSTED GROSS INCOME) \< \[PARAMETER\])**

When the software reads this query and comes to the box
**\[PARAMETER\]** it will ask you to provide a value for Student's
Adjusted Gross Income.

Now that we know the **Terms** and the **Rules**, we can begin our
discussion of queries.

######## 

######## Predefined Queries

The Quality Analysis Tool comes with a set of twelve pre-defined
queries. These queries are commonly used. However, you may use
predefined queries as templates to create your own queries.

When you open the Query dialog box for the first time, you will see the
predefined queries are stored as the first set of records in the Query
database and they are labeled: "PREDEFINED QUERY" in the upper right
corner of the Query dialog box. **You cannot delete a predefined
query.**

![](media/image3.png)Select **File, Tools, Query:** Use the arrow button
in the database buttons box to locate the predefined query you want to
use.

Another way of accessing predefined queries would be to CLICK the
RETRIEVE button in the Query dialog box. All queries will be displayed.
All predefined queries will be identified by a 'checked' box.

![](media/image4.png)

Building a Simple Query: In this demonstration, we build a simple query
to select all records that have Parental AGI less than or equal to
\$30,000.

+-------+--------------------------------------------------------------+
| #     |                                                              |
|       |                                                              |
| # S   |                                                              |
| tep A |                                                              |
| ction |                                                              |
+-------+--------------------------------------------------------------+
| 1     | Select **Tools** -- **Query** from the menu bar. The query   |
|       | Dialog box displays.                                         |
+-------+--------------------------------------------------------------+
| 2     | A blank record displays. Select **Add** to build a new       |
|       | query.                                                       |
+-------+--------------------------------------------------------------+
| 3     | Enter a **Title** -- something descriptive that readily      |
|       | identified the query. In this case, enter **Parent's AGI     |
|       | less than or equal to \$30,000**                             |
+-------+--------------------------------------------------------------+
| 4     | Click the **Field** down arrow to see the list of available  |
|       | fields in the Quality Analysis Tool software. With the       |
|       | cursor, select the desired field.                            |
|       |                                                              |
|       | In this case, select **Parent's Adjusted Gross Income**      |
+-------+--------------------------------------------------------------+
| 5     | Select the appropriate **Operator** by clicking the **down   |
|       | arrow** to see the full list.                                |
|       |                                                              |
|       | In this case, select \<=                                     |
+-------+--------------------------------------------------------------+
| 6     | Enter the **Value** to be measured against the field you     |
|       | selected.                                                    |
|       |                                                              |
|       | In this case, enter 30,000                                   |
|       |                                                              |
|       | (If you want to make this a Parameter Query, you would leave |
|       | the Value field blank and **mark** the **Prompt at Executive |
|       | box.)**                                                      |
+-------+--------------------------------------------------------------+
| 7     | Click the **Append** button to add the statement to the      |
|       | query. The following query statement displays:               |
|       |                                                              |
|       | (Parent's AGI \<= 30,000)                                    |
+-------+--------------------------------------------------------------+
| 8     | Click **Save**, to save the query. A Record Updates Saved    |
|       | dialog box displays.                                         |
+-------+--------------------------------------------------------------+
| 9     | Click **OK** to return to the Quality Analysis Tool main     |
|       | menu.                                                        |
+-------+--------------------------------------------------------------+

Next, you will need to decide on a report to run to give you the
information you want to see for the students you have selected using
this query.

Building a Simple Query (continued): You have created a simple query to
select all records with Parent's AGI less than or equal to \$30,000.

Next, test your query by printing a report. Let's use a Custom Report to
display the last name, first name, SSN, and Parent's AGI for the
students selected using this query.

+-------+--------------------------------------------------------------+
| # S   |                                                              |
| tep A |                                                              |
| ction |                                                              |
+-------+--------------------------------------------------------------+
| 10    | Select **Tools -- Custom Report** from the menu bar.         |
+-------+--------------------------------------------------------------+
| 11    | Enter a Code. Type **01**                                    |
+-------+--------------------------------------------------------------+
| 12    | Enter a **Description.** Type Parent's AGI less than or      |
|       | equal to 30000                                               |
+-------+--------------------------------------------------------------+
| 13    | Select **Field Descriptions.** Click Last Name - Demo, First |
|       | Name - Demo, Original SSN, Parent's Adjusted Gross Income    |
+-------+--------------------------------------------------------------+
| 14    | *Optional:* Click **Resequence.** This will move all of the  |
|       | Fields you selected to the top of the Field Description      |
|       | column.                                                      |
+-------+--------------------------------------------------------------+
| 15    | Click **Save**                                               |
+-------+--------------------------------------------------------------+
| 16    | Click **OK** to return to the Quality Analysis Tool main     |
|       | menu.                                                        |
+-------+--------------------------------------------------------------+
| 17    | Select **File - Print**                                      |
+-------+--------------------------------------------------------------+
| 18    | Select a Report. Click **Custom Report**                     |
+-------+--------------------------------------------------------------+
| 19    | Select a Report File Destination. Click Printer or File or   |
|       | Screen.                                                      |
+-------+--------------------------------------------------------------+
| 20    | Click ellipsis button near **Custom Code.** Select 01.       |
+-------+--------------------------------------------------------------+
| 21    | Click **OK** to leave the dialog box.                        |
+-------+--------------------------------------------------------------+
| 22    | Select **Last Name** under Sort Order.                       |
+-------+--------------------------------------------------------------+
| 23    | Click **Selection Criteria.** Click the Query Title ellipsis |
|       | button. Select **Parent's Adjusted Gross Income.**           |
+-------+--------------------------------------------------------------+
| 24    | Click **OK** twice to leave the dialog box.                  |
+-------+--------------------------------------------------------------+
| 25    | Click **OK** to initiate printing.                           |
+-------+--------------------------------------------------------------+

# 

Whenever the Selection Criteria option is available in QA Tool (for
example, in Print), you can use these queries to identify groups of
records.

# Custom Report produced by the Simple Query

# 

![](media/image5.png){width="6.558333333333334in"
height="4.748611111111111in"}

The Custom Report generated using the query displays the requested data
for those students whose Parental AGI is less than \$30,000.

There are many standard reports available in the Quality Analysis Tool
software. Refer to the **Report Section** of this document to become
more familiar with the various types of report the software has to
offer.

Remember:

Query + Appropriate Report = Data you want to study

# 

# 

######## What Is a Complex Query?

A complex query is formed by linking two or more simple queries together
and using an **AND** or an OR connector. Complex queries provide you
with a powerful tool for working with your Quality Analysis Tool
database.

# AND

# Both segments of the query connected by AND must be true for a record to be selected.

##### Think of two overlapping circles, each circle representing the records selected by a 

query statement. When an AND connect is used, the query selects only
those records

that represent the overlap of the two circles. For example, the
following query would

only select independent students that have an EFC of O:

Dependency Status = "I" **AND** EFC = 0

![](media/image6.png)

Independent students without an EFC equal to 0 and dependent students
would not be included in the query.

#### OR

Only one of the segments connected by OR must be true for a record to be
selected. Let's used the overlapping circles again diagram again. When
an OR connector is used, the query selects all records represented by
the two circles, not just records represented by the overlap of the
circles.

####### Order of Precedence

You can use OR and AND connectors in the same query. Be sure to keep in
mind the order of precedence. Queries are processed from left to right;
however, AND is applied first, even if it appears after OR in the query
statement.

**Parentheses**

Use parentheses to further define the order of precedence. Additional
parentheses create an expression by connecting two or more fields. You
can use the expression in a query statement as you would use a field.

Building a Complex Query: In this demonstration, we build a complex
query to select all records where Student's Total Worksheet A is less
than 1000 and Parent's Number in College equals 2 and EFC is less than
3000. Let's build this query with a parenthesis.

+-------+--------------------------------------------------------------+
| # S   |                                                              |
| tep A |                                                              |
| ction |                                                              |
+-------+--------------------------------------------------------------+
| 1     | Select **Tools** -- **Query** from the menu bar. The query   |
|       | Dialog box displays.                                         |
+-------+--------------------------------------------------------------+
| 2     | A blank record displays. Select **Add** to build a new       |
|       | query.                                                       |
+-------+--------------------------------------------------------------+
| 3     | Enter a **Title** -- something descriptive that readily      |
|       | identified the query. In this case, enter **Student's        |
|       | Worksheet A and EFC and Number in College**                  |
+-------+--------------------------------------------------------------+
| 4     | Click the **Field** box. Select **Student Worksheet A**      |
+-------+--------------------------------------------------------------+
| 5     | Select the appropriate **Operator.** Select **\<**           |
+-------+--------------------------------------------------------------+
| 6     | Enter the **Value**. Enter 1000                              |
+-------+--------------------------------------------------------------+
| 7     | Click the **Append** button to add the statement to the      |
|       | query.                                                       |
+-------+--------------------------------------------------------------+
| 8     | Click the **Field** box. Select **Parent's Number in         |
|       | College**                                                    |
+-------+--------------------------------------------------------------+
| 9     | Select the appropriate **Operator** box. Select **=**        |
+-------+--------------------------------------------------------------+
| 10    | Enter the **Value**. Enter 2                                 |
+-------+--------------------------------------------------------------+
| 11    | Click the **Append** button to add the statement to the      |
|       | query.                                                       |
+-------+--------------------------------------------------------------+
| 12    | Click the **Field** box. Select **EFC**                      |
+-------+--------------------------------------------------------------+
| 13    | Select the appropriate **Operator** box. Select **\<**       |
+-------+--------------------------------------------------------------+
| 14    | Enter the **Value**. Enter 3000                              |
+-------+--------------------------------------------------------------+
| 15    | Click the **Append** button to add the statement to the      |
|       | query. A three-statement query displays.                     |
+-------+--------------------------------------------------------------+
| 16    | Click on the first line of the query, then click the **add   |
|       | Left button**                                                |
+-------+--------------------------------------------------------------+
| 17    | Click on the second line of the query, then click the **add  |
|       | Right button**                                               |
|       |                                                              |
|       | The first two statements are nested within parentheses, and  |
|       | will be processed first.                                     |
+-------+--------------------------------------------------------------+
| 18    | Click **Save**, to save the query. A Record Updates Saved    |
|       | dialog box displays.                                         |
+-------+--------------------------------------------------------------+
| 19    | Click **OK** to return to the Quality Analysis Tool main     |
|       | menu.                                                        |
+-------+--------------------------------------------------------------+

Next, **test your query** by printing a report. You may select an
appropriate standard report or use a Custom Report. You may follow
instructions on page 6 of this section and select Field Descriptions you
would like to see for these selected students.

###### Modifying a Query

To modify a query:

+-------+--------------------------------------------------------------+
| # S   |                                                              |
| tep A |                                                              |
| ction |                                                              |
+-------+--------------------------------------------------------------+
| 1     | Select **Tools** -- **Query** from the menu bar. The query   |
|       | Dialog box displays.                                         |
+-------+--------------------------------------------------------------+
| 2     | Use the arrow buttons in the database buttons box to locate  |
|       | the query you want to modify.                                |
+-------+--------------------------------------------------------------+
| 3     | Modify the query. Click any line to select it and change the |
|       | Field, Operator, or Value. Then click the **Change button.** |
|       |                                                              |
|       | **-**You can also delete lines or add new lines to the       |
|       | query.                                                       |
|       |                                                              |
|       | -To add new lines, select the line that should appear after  |
|       | the ***new*** line.                                          |
|       |                                                              |
|       | Specify the Field, Operator, and Value for the new line, the |
|       | click **Append.**                                            |
|       |                                                              |
|       | **-**Use the other buttons located around the Criteria box   |
|       | to fine-tune the                                             |
|       |                                                              |
|       | query statement if necessary.                                |
+-------+--------------------------------------------------------------+
| 4     | To delete a line, select the line and click **Remove**       |
+-------+--------------------------------------------------------------+
| 5     | Click **Save** to save the query, then Click **OK.**         |
+-------+--------------------------------------------------------------+
| 6     | Click **OK** to close the Query dialog box or click **Add**  |
|       | to create another query.                                     |
+-------+--------------------------------------------------------------+

These procedures may be used to modify a predefined query or modify a
query you have created.

Although predefined queries maybe used to create new queries, remember
predefined queries cannot be deleted.

**What's New!**

-   A *New predefined query on EFC tolerance*

![](media/image7.png){width="5.43125in" height="4.179861111111111in"}

The **EFC Difference** is a new predefined query in the 2002-2003
Quality Analysis Tool software. This predefined query will allow you to
establish an EFC tolerance.

In the graphic above, you see that this is a "Prompt at Execution"
query. When you run the query you will be asked to enter a value for the
EFC Difference you want to study, e.g. 500. This predefined query, when
used in combination with the desired report, will display a list of
students whose EFC change equals \$500.

What's the difference between the "EFC Changed" and the "EFC Difference"
predefined queries?

The "EFC Changed" predefined query, when used with an appropriate
report, provides all students whose EFC changed from initial to paid-on
transaction. The "EFC Difference" predefined query allows you to specify
the EFC tolerance you want to study.

-   A *New* **Field to Field** Comparison

![](media/image8.png){width="5.995833333333334in"
height="4.6194444444444445in"}

You can compare numeric fields within records using the new Field to
Field Comparison Option. This query option allows you to compare one
field to another field.

In the graphic above, records are selected if the Parent's U.S. Income
Tax Paid equals 10 percent of Parent's Adjusted Gross Income.

######### Important Points

1.  Mark the checkbox to build the "Field to Field Comparison" query.

2.  Fields are compared within the same ISIR transaction number. For
    example, a field to field query compares the numeric fields in the
    01 transaction only with other numeric fields in the 01, not with
    the numeric fields in the student's 02 transaction.

3.  The Field to Field Comparison option is not available if you marked
    "Prompt at Execution" or Current Date.

######## Query Management Report

![](media/image9.png){width="6.0in" height="4.5in"}

The Query Management Report is a list of both the queries you have
created and the predefined queries provided with the QA Tool software.
This report is locate under: **File, Print.** Under Report **--** select
**Query Management.**

All queries may be found in the Query dialog box which you will find
under **FILE, TOOLS, QUERY.** However, you must use the arrow keys to
scroll through each query in your database.

This report display all queries in a list format and indicate which are
PARAMETER or PREDEFINED queries.

######## 

######## Summary

-   Queries used to select a specific group of students/records in your
    Quality Analysis Tool database.

-   Queries are built. They are built using three components:

    -   A data field

    -   An operator

    -   A value

> Queries are further defined by connectors, such as AND, OR,
> parentheses and parameter values, Like, etc.

-   The Quality Analysis Tool has twelve Predefined Queries that cannot
    be deleted.

```{=html}
<!-- -->
```
-   Queries may be modified or expanded upon to produce the precise
    selection tool that you need.

```{=html}
<!-- -->
```
-   **NEW for 2002-2003**: **EFC Difference Predefined Query and Field
    to Field Comparison Query**

Remember:

Query + Appropriate Report = Data you want to study

# Section 2: Learn how to write Verification Edit Profiles using queries.

If you would like to see what the impact was on the Verification that
was applied to your student records, you can write the applicable
Verification Edit Profiles and enter it into the Tool . Once entered,
you can apply it to your student records and run reports to determine
their effectiveness.

**Helpful Pointers:**

-   Develop a written list of your Verification Profiles.

-   Develop a Title for each Profile. The Title Box on the Query Screen
    does not allow for spaces or symbols. If you have numerous
    Verification Profiles, your 'naming' mechanism may be critical.

-   Become familiar with the data fields listed in the "Field" box on
    the Query Screen. The "Field" box contains:

    -   All FAFSA data fields,

    -   All fields that are by-products of the EFC calculation, and

    -   Any user-defined fields that you create

-   Construct your Verification Profile queries with the Query Screen
    Data Fields in mind.

    -   

**Example:** A Verification Profile might be: All Single students with
dependents. It will take 3 data fields to construct the Query.

-   Is Student Married? = 2

```{=html}
<!-- -->
```
-   Have children you support = 1, OR

-   Have dependents other than children or spouse = 1

```{=html}
<!-- -->
```
-   Special Note: The Household Size data field requires "2" digits. If
    you are entering 3 in the household, you would enter "03".

-   Determine which User-Defined fields are needed before attempting to
    construct you Verification Profile queries.

-   Run a "Query Management" Report to get a listing of all your queries
    to review the queries you have constructed.

**Steps in Constructing Verification Profiles**

**First:** Construct a query for the profile.

**Second**: Use the query as a Verification Profile.

**Third:** Confirm that verification has been entered into the Quality
Anlaysis (verification) Tool . You may choose to construct all th
queries for you Verification Profiles first, then complete Steps 2 and
3.

Listed below are 8 examples of Verification Edit Profiles that you can
review. Just click on the Verification Edit Profile that you would like
to review and it will link you to how to construct the Verification Edit
Profiles.

**[Verification Edit Profile
1](http://qaprogram.air.org/download/Profile 1%5B1%5D.ppt):** Dependent
Students with parents AGI\<=0

**[Verification Edit Profile
2](http://qaprogram.air.org/download/Profile 2.ppt#_blank):**
Dependent/Resident student with Parental Taxes Paid\>25%of AGI and
EFC\<12546.

**[Verification Edit Profile
3](http://qaprogram.air.org/download/Profile 3.ppt):**
Dependent/Non-resident student with Parental Taxes Paid\>25% of AGI and
EFC \<21760.

**[Verification Edit Profile
4](http://qaprogram.air.org/download/Profile 41.ppt):** Independent with
dependents other than a spouse, AGI and Untaxed Income \<11610.

**[Verification Edit Profile
5](http://qaprogram.air.org/download/Profile 51.ppt):** Dependent with
Income Exclusions\>20% of AGI.

**[Verification Edit Profile
6](http://qaprogram.air.org/download/Profile 61.ppt):** Independent with
Income Exclusions \>20% of AGI.

**[Verification Edit Profile
7](http://qaprogram.air.org/download/Profile 71.ppt):** Orphan/Ward of
the Court (New &Transfers Only).

**[Verification Edit Profile
8](http://qaprogram.air.org/download/Profile 8%5B1%5D.ppt):** CPS
Verification Edit Profile

Note: If your institution has not developed

specific verification selection criteria and

your institution uses the federal edits as its

only verification selection criteria,

> Verification Edit Profile 8 is the only profile
>
> you will need.

Section 3: **Enter and apply your verification Edit Profiles into the
Quality Analysis (verification) Tool.**

Click on the following link to learn how to apply and enter your
Verification Edit Profiles into the Tool.

[Apply and Enter your Verification Edit
Profiles](http://qaprogram.air.org/download/Apply and Enter Verification Edit Profiles.ppt)