The following documentation provides information on some of the general functions available to you through Query. To begin the query process, click on the Query node in Aeries.
Various commands can be selected by using the mouse or typing the query by hand.
The commands available are:
- LIST – will select and display the query data.
- TOTAL - will calculate a total amount for a specific category and display.
- SKIP - will bypass specific records.
- KEEP - will select specific records.
- CHANGE - will make permanent changes to select records. It is recommended that this command is restricted until a User is advanced.
NOTE: It is suggested that BEGINNING query users only utilize LIST and TOTAL commands until comfortable with query.
A text box displays at the top of the form. This area is where the query will be built. Below the text box, a message displays indicating the steps to be performed.
Creating a Basic Query
The first step when creating a query is to Enter a Command from the Commands box which is highlighted in blue.
Clicking on a command will display it in the text box. Notice the message has changed and now displays Enter table code. Take note to the column called Tables. The blue highlight has shifted from the Commands column to the Tables column indicating that selecting a table is the next step in creating the query.
All of the tables that are available will display a 3 or 4 letter code to the left of the list. The Table Name displays to the right of the code. The scroll bar on the right side of the box is used to display additional tables.
To create a Student related query click on the STU table. The 3-letter table code will display in the text box. The TABLES list will now only display tables related to the STU table. Typically the link between the STU table and other tables will be the Student Number (SN) or Student ID (ID)
The next available step(s) that can be performed will display below the text box. Notice that there are many different steps listed and highlighted in blue.
Run Query for STU Table
With only the STU table selected, click on the RUN button. A datasheet will display with ALL data stored in the table for each active student. A scroll bar displays at the bottom of the datasheet. Use the arrows and slide to the right to display ALL additional data.
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 set of results.
To return to QUERY from from the results, click on the “X” to close the pop-up window. The Query form will re-display and still contains the original query statement generated. Changes can be made to this query and can be RUN again to change the data that you are looking for.
Run Query for Specific Fields in STU Table
The Fields list also contains two or three letter codes along with the field description of each field in the selected table(s). To generate a query for specific fields click on the Field Names. The field code will display to the right of the table in the text box.
The query displayed below, LIST STU SN LN FN SX GR, contains the fields for the Student Number, Last Name, First name, Sex and Grade for all active students. Notice that there is a space after each entry of the query. As you select each field with the mouse, a space is automatically inserted. In order for the query to generate properly there MUST be a space in between each entry of the query statement.
The following is the definition for the query:
LIST = display on the form
STU = from the Student Data table
SN = the student Number
LN = the students Last Name
FN = the students First Name
SX = the Sex
GR = the current Grade
After the query statement is entered click on the RUN button and the query results will be generated. A datasheet will display with data from the fields selected. Notice the first and second student listed.
The first student’s number is greater than the second student’s number. This is because the default query sort order is by the students Last Name, First Name.
Change Default Sort Order
Now that the data has been selected it can be sorted by a particular field, for instance by grade. To sort the query click on the BY option under SORT.
Determine the order that you want this data sorted. Click on the field, which will display to the right of BY in the query statement. For example, BY GR will sort the data in order of grade instead of the default last name, first name. Click on the RUN button. The datasheet will now display these students in order by grade. Click on the “X” in the far right corner to return to Query.
Select Specific Records
To select specific records, such as females only, an IF option can be added to separate the data. For this query to select certain records, click on IF. Click on a field for record selection. Type the criteria necessary to select a specific record. For example, for a list of only female students, enter: IF SX = F
Click on the RUN button. Students displayed are only females and still in grade order. After generating a query verify the data selected is correct and in the right order. Then you will need to decide what you want to do with this data.
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 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).
TOTAL Queries
The TOTAL command can be used to add together a specific series of students and create a running total on the screen. For example, to determine the number of students in each grade you would type the following TOTAL query selecting the grade field and sorting by the grade field.
Take note to the SORT order selected, BY GR. Also note that the field the query is sorting on must be included in the TOTAL query statement. Click on the RUNbutton. The following calculation will be generated.
The TOTAL command can also be used to add together a specific series of students and create a running total on the screen using more than one field. For example, to determine the number of students for each teacher/counselor by grade you would type the following TOTAL query selecting the teacher/counselor and grade field and then sorting by the teacher/counselor and grade field.
Take note to the SORT order selected, BY CU GR. Also note that the fields being sorted on must be included in the TOTAL query statement. Click on the RUN button. The following calculation will be generated.
Include Inactive Students
When a query is generated only the active students are included by default. To include inactive students, click on the Include Inactive Students check box. When you generate a query ALL students will now be included in the results.
Error Messages
If a command, such as LIST is used but an incorrect table name is entered into the query statement an error message will display. Take note to the Command Line in the query below and notice that it is displaying an error message. The message states Invalid table code because in the statement above, XYZ was entered after the LIST command and then RUN was selected. Click on the NEW button and re-enter the query statement.
Conditions and Comparisons
| LIST STU ID FN LN GR RC1 BY GR RC1 | Display student ID, first name, last name, grade, and race. Order by grade and race. |
| LIST STU ID FN LN GR IF GR = 12 | Display student ID, first name, last name, and grade if in twelfth grade. |
| LIST STU FN LN GR SX IF GR = 12 AND SX = M | Display first name, last name, grade, and sex if in twelfth grade and male. |
| LIST STU FN LN GR SX IF GR = 10 OR GR = 12 | Display first name, last name, grade, and sex if in tenth or twelfth grade. |
| LIST STU FN LN GR SX IF GR > 10 | Display first name, last name, grade, and sex if in grades greater than tenth grade. |
| LIST STU FN LN GR SX IF GR < 11 | Display first name, last name, grade, and sex if in grades less than eleventh grade. |
| LIST STU FN LN GR SX IF GR # 10 | Display first name, last name, grade, and sex for all grades EXCEPT tenth grade. |
| LIST STU FN LN GR SX IF LN : SON | Display first name, last name, grade and sex if their name contains "SON" anywhere within it. |
| LIST STU FN LN GR PG IF PG ; LN | Display first name, last name, grade, and parent/guardian if the parent/guardian name does not contain the student’s last name. |
Using Query to Proof your Work
| LIST STU ID FN LN RC1 BY RC1 | Display students in order of race codes with blank race codes at the top of the list. |
| LIST STU ID FN LN IF CU = 0 | List students without a teacher or counselor number. IF CU = 0 is a zero, not the letter “O”. |
| TOTAL STU CU BY CU | Elementary setting - Total number of active students for each teacher. Secondary setting - Total student distribution across the counseling staff. |
| TOTAL STU RC1 GR BY RC1 GR | Total number of active students in order by race code and grade. |
| LIST STU ID FN LN ETH RC1 IF ETH = " " AND RC1 = " " | Display students that have blanks in the Ethnicity Indicator field and Race field. |
Additional Data to Query in Aeries
| LIST STU ID FN LN NS NG NT | Display students' next school, next grade and next teacher information. |
| LIST STU ID FN LN SX GR BD CU BY BD CU | Display students' birthday sorted in order by birthday and teacher/counselor. |
| LIST TCH TN ID TE TT EM RM | Display specific Teacher information. |
| LIST CRS CN CO NA S1 DE LO HI E1 | Display specific Course information. |
| LIST COL ID NM AD CY ST ZC TL CD ZX AT | Display specific College information |
| LIST LOC CD NM AD CY ST ZC AC TL LO HI | Display specific information for all schools in the district. |
| LIST LKR LK CM SN C1 C2 C3 C4 C5 SE | Display specific Locker information. |
| LIST EMP CD NM AD CY ST ZC ZX TL | Display Employers set up for Work Permits. |
| LIST REQ CD SA C1 C2 C3 C4 GRT | Display Graduation Requirement information. |
| LIST CTL ID PT NM A1 GR BY ID PT | Display all Tests sorted by the Test ID and Part Number. |
Extended Student Fields for Query
The use of Extended Student Query Fields is an advanced topic of Aeries Query that seamlessly integrates data from Aeries Analytics into query results with no complex syntax. In Aeries Query, the Extended Student Fields become available once the STU table has been included in the query (e.g., LIST STU). Additional Information can be found here regarding Extended Student Fields for Query.
