Query Statements - Using One Table

A Query Statement can be created with one or more tables.  The following query statement utilizes one table, which only accesses data from the STU table. When the query is generated the following will display with the data selected.

Using Multiple-Table Queries

A Multiple Table Query uses more than one table to access different information.  For example, the query below will create a list of students with a medical condition by accessing data from the STU and MED tables.

Notice that when more than one table is used in a query the table name comes before the field name.  For example, MED.CD.  This is due to the possibility of two tables having a field with the same name but containing different data.  The following is the definition for this query:

    LIST                            =  display on the screen
    STU  MED                  =  using two tables, student data and medical
    STU.SN                      =  student number from the STU table
    STU.LN                      =  student last name from the STU table
    STU.FN                      =  student first name from the STU table
    MED.CD                    =  medical code from the MED table
    MED.CD?                  =  medical code description from the COD table
    IF MED.CD  #  " "      =  if the medical code is not blank

This Query will display the student number, last name, first name, medical code, and medical code description for students with a medical condition.

Viewing Data from the Datasheet View

After a query is generated the data will display in a data grid. To maximize the form and View the Data click on the middle box in the right corner. The data will then display maximized on the screen.

Aeries returns results using a pop-up window with scroll bars to the right and the bottom of the window. A maximum of 400 records will display per page. Clicking on the page numbers at the bottom of the window will display the next page of results.

Sort the Results

In Aeries clicking on the column header will sort the data in ascending order by that column. Clicking it a second time will sort the data in descending order by that column, and clicking it a third time removes the sort on that column. 

Filter the Results 

To filter for specific data use the Filter icon in a column header. Enter the value to filter for and click on the Filter button. The records matching the filter criteria will display. To remove the filter selection click on the Clear button.

Keep the Students 

Clicking on the Keep Students bar at the top of the Query results will redirect you to the Student Profile page and create a temporary filter on the STU table that will limit the students that can be viewed to those in the query results. Click on the Reset button in the navigation or in Query to remove the filter.

Special Characters

There are Special Characters that can be used within a query statement that will provide shortcuts to the final query generated.  These characters are displayed on the right hand side of the form under Special.

The following is an example of a query utilizing Special Characters listed above. Notice that the NM function joined the Last Name and First Name together.  The MI function stripped off all letters from the Middle Name and only displayed the first initial.

After the query statement is closed and Labels has been selected a report for Avery 5160 labels will display.  The \ has forced a new line and Barcode has converted the student ID number into a scanable barcode.  

The question mark (?) will pull the Code Descriptions for the field from the COD table.  However the code values for every field are not ALL stored within the COD table for that field. There are some fields that contain identical codes and descriptions as another field. In this case the code values and descriptions for both fields will be saved under a single field in the COD table.

For example, the Correspondence Language field will pull the codes and descriptions from the Reporting Language field.  The query is created a little differently.  After entering the field name and question mark (?) you will need to add the field where the codes are stored. The query below has: CL?HL which will have the Correspondence Language (CL) field pull the codes and descriptions from the Reporting Language (HL) field.

The DAY, MONTH, YEAR and YEARS are functions that will allow you to split the day, month or year from a Date field and can also total the number of years between a Date field and today’s date. 

The query below has ED.DAY ED.MONTH ED.YEAR in the query and when selected will only display the Day, Month or Year of the Enter Date (ED) field.  The ED.YEARS function calculates the total number of years from the Date field using today’s date.

Total Query Statement

The TOTAL command will perform a count of select data generated in a query statement. When running a TOTAL query the fields selected MUST be followed with a BY statement and ALL the fields selected.  For example:  TOTAL STU SX GR BY SX GR.

This query will generate a count of the number of male/female students in each grade level. 

Reports can also be generated for TOTAL queries using the Report button.

Additional Functions

The buttons to the right of the Query form can be used after the query has been generated to create reports, labels, files, etc.  

Below is a description of the what each does:

• NEW clears the text box area of any query currently displayed to enter a new query.
• RUN will generate the query statement and display the data. 
• EXCEL will create an Excel spreadsheet from the data generated. This option is only available in Aeries Web Version.
• REPORT will generate a formatted report from the data generated.
• LABELS will generate formatted labels from the data generated.
• LETTERS will generate a formatted letter previously created in the Query Text Editor program from the data generated.
• LETTER EDITOR will store formatted letters created in the Query Text Editor program.
• ENVELOPE will generate the data in a format that can be printed on a legal envelope.
• TO FILE will create a text file onto a disc from the data generated. This option is only available in Aeries Client Version.
• CHANGE allows you to make quick changes to data in select fields.
• SAVE allows you to save the query for later use.
• LOAD will allow you to re-load a query that was previously saved.
• STARTUP will store a KEEP statement that can be set up to run automatically on the opening of Aeries WITHOUT having to enter query. This option is only available in Aeries Client Version.
• RESET will reset the KEEP and SKIP statements. 
• TABLES will re-load and update data tables. This option is only available in Aeries Client Version.
• EXIT will exit the QUERY form. This option is only necessary in Aeries Client Version.
• SHOW SQL only is available for admin user types. When this option is selected the SQL statement that is built behind-the-scenes for the Aeries query is displayed.
• Db Defs will show table definitions and table relationships (Database Definition page).

Using Query Letter Editor

The LETTER EDITOR allows you to create letters that will contain text along with merged data from a query statement. Once the letter has been created in the Query Letter Text Editor, a query statement must be generated to pull the data from the tables and fields selected. The LETTER button is utilized to display the letters available.  The letter is selected and the data is merged into the letter.

In Aeries a list of letters appears to the left of the Letter Editor. Click on the Letter ID to view a letter. The Add button can be used to add a new letter.

Prior to creating a letter it must be determined which fields will be used to populate the letter.  Click on the LETTER EDITOR button. Click on the Add button and enter the letter name in the Letter: field.  Press Tab and the Author: field will automatically display the current user’s name.  Press the Tab key until the cursor displays at the top of the white text box.

The heading of a letter usually contains who the letter will be sent to, for example to the Parent.  On the first line type [PG] for the Parent/Guardian name and press Enter.  The cursor will drop down to the second line.  Type [AD] for the address and press Enter.  The cursor will drop down to the third line.  Type [CY]  [ST]  [ZC] and press Enter.

A school heading for the letter will automatically be created with the school name, address, and the current date at the top of the letter.  This heading will not display in the letter editor but will print when submitted.

NOTE:   ALL field codes MUST have a square bracket surrounding them and there must be a space between each field but not within the brackets.   

Type the remainder of the letter combined with text and any field codes.  An example of a letter is displayed below.  A query function is available that will insert [he/she] or [his/her] into the letter for the proper sex but the sex field must be used in the query statement.

For example, in the last sentence where him/her is used, if the sex code for a student is F the above letter will read “we would like to have her join us”.

After the letter is complete, click the Save button at the bottom of the form. A query can now be generated through the Query option and the Letter function is utilized to generate the letters. 

NOTE: Each field used MUST be in the query statement or will be blank when the letter prints.  

For more detailed information on the Query Letter editor please see this article:  Query Letters

Letters

The LETTERS option is used to generate a formatted letter created in the Query Letter Editor.  Fields are added into the content of the letter.  When the query is generated these fields will be included in the query statement to be merged into the letter.  After all fields are selected, including the SX field for the [him/her] statement, click on the RUN button.  Generate the query and verify all necessary data was selected and close the query statement.

NOTE: ALL fields setup in the letter MUST be in the query or will be blank when the letter prints.

Click on the LETTERS button and the following selection box will display. Click on the letter that you wish to generate in the Letter ID column.  If pre-printed letterhead will be used select the Using preprinted letterhead paper checkbox. Select where on the letter the date will be printed and then click on the Run Report button.

The letter will be generated and the data selected in the query will be merged into the letter.  The following is an example of the Gate Letter.  Notice the SX field was selected in the query statement and HER was entered into the letter due to this student being a female.

REMEMBER - ALL fields in the letter MUST be in the query statement or will be blank when the letter prints.  If a field in the letter is not in the query, then the letter will have a blank in the field area. For example, if the city (CY) field was not selected for a letter the City would be blank in the address.