The Query document is a part of the Aeries.net User Manual that contains easy step-by-step instructions on how to utilize Query.
|[Query - Aeries Web Version - Updated November 18, 2016]|
Aeries Web Version - Query
All data entered into Aeries is stored within tables in the database. The Query option enables you to access the data stored in these tables. The process generates a "query statement", which is somewhat like a program and collects the data from within the tables selected. You can then use this data to create a report or to update specific data. Query is a very visual program, which makes it extremely easy to use when accessing data.
What is a Table?
A table is a file that stores certain data entered into the Student Information System. The Query form below displays some of the tables that are contained within the database.
How is a Table Created?
The form displayed below is the Teachers form. After you have typed information into the different fields on this form and pressed ENTER the information creates one record and is saved in the TCH table.
After the first record is created, essentially you have created the TCH table that will now store all new records. As you keep adding teachers a new record is created and a different teacher number is assigned.
Why is There More than One Table?
Instead of creating one large table containing all of the school's data, smaller tables are created to help increase processing speed. In order to access and combine data within a query a connection must be made between the two tables, which is usually the student number.
For instance, when medical data is entered for a student the student number, medical code, details, etc. are added to the table. Using Query to access and combine this information with the STU table the student number field is used and creates a link between the two tables.
The MED and STU tables are displayed below with all fields and both have the SN field that links the tables.
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 Web Version.
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 BEGINNING queries only utilize LIST and TOTAL commands until comfortable with query.
Creating a Basic Query
The first step when creating a query is to Enter a Command from the Commands box which has an aqua blue border. The border of the boxes will change to indicate each step to be taken.
Click the selected command which will display in the text box. Notice the message has changed and now displays Enter Table Code. Take note to the outline on the box called TABLES, which has changed to an aqua blue border.
All tables available display a 3-letter code under TBL. Table Name displays to the right of the TBL code. A scroll bar displays on the right side of the box 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 box will now only display tables related to the STU table that have a LINK, such as the Student Number (SN).
The next available steps that can be performed will display below the text box. Notice that there are many different steps listed.
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 Web Version 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 Aeries Web 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.
Change Sort Order of Field Names
Take note to the FIELDS box which displays ALL fields available for the STU table and the box is outlined in an aqua blue border. A two or three letter code displays for all fields under the FLD heading and display in the order stored in the table. The Field Name displays to the right. To change the sort order, click on the Field Name heading and the sort order will change to alphabetical A-Z.
To display the fields in reverse alphabetical order, click on the Field Name heading again and the order will change from Z-A. A third click will return the order to the original view.
Run Query for Specific Fields in STU Table
To generate a query for specific fields, click on the selected Field Names. The two or three letter 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 click 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 selected:
|= display on the form|
= from the Student Data table
= the student Number
= the students Last Name
= the students First Name
= the sex
= the current grade
After the query statement is entered click on the RUN button and the query will be generated. A datasheet will now 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 that 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 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 other data. For this query to select certain records, click on the IF option. 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.
Print a Datasheet Report
To generate a report in the same format as the datasheet displayed on the screen click the printer icon at the top of the Query form in Aeries Client Version and a report will be generated.
There are a variety of options available in the column on the right side of the QUERY form. These buttons can be used after the query has been generated to create reports, labels, files, etc.
The following are the Additional Functions available:
- 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 a format to be printed on a legal envelope from the data generated. This option is only available in Aeries Client Version.
- 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. This option is only available in Aeries Client Version.
- SAVE allows you to save the query for later use.
- LOAD will allow you to re-load a query 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.
Creating a Report
After generating a query statement, a formal looking report can be created by clicking REPORT. To format the report, enter the report title name to print at the top of the report and select the line spacing. If this report was sorted BY a specific field, such as grade, select whether or not you want this report to have a page break for each grade level. Click on the OK button.
There is also an additional Report Format and Report Delivery option. Reports can be formatted as a PDF, Word Document (RTF), Spreadsheet (XLS) or Text file (TXT).
The Report Delivery options are:
- Email w/ Attachment – an email is sent to the user notifying them of the report parameters and the completion of the report
- Email w/o Attachment – an email is sent to the user notifying them of the report parameters and the completion of the report along with an attachment of the report
- None – no emails are sent to the user
The following report will display. Since it was sorted by grade and Page Break was selected, each GRADE will be printed separately. The Grade number will print at the top of the page after the report name. To print the formatted report displayed, click the Printer icon at the top of the form:
Once a query has been created it can be saved and accessed again at a later time. To save a query, click on the SAVE button.
The following text box will display. Enter a descriptive name in the text box. Click the OK button to save your Query.
REMEMBER: If a REPORT query is saved the query name will print on the report heading.
Loading Saved Queries
To LOAD a saved Query statement, click the LOAD button on the QUERY form. The saved queries can be displayed by the name of the Query or by the Author's name. To select the Query and run it, click on the query record.
NOTE: We do suggest ONLY saving frequently used Queries or complicated Queries.
The Query statement will now display in the Query textbox and can be generated by clicking on RUN. The query will be generated and will display the data displayed.
The TOTAL command can be utilized 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 the RUN button. The following calculation will be generated.
The TOTAL command can also be utilized 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 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 the Include Inactive Students check box. When you generate the query ALL students will now be included in the data selected.
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 the Error message. The message states Invalid table code. In this case, the STU table is missing from the query. Click the NEW button and re-enter the query statement.
Conditions and Comparisons
|LIST STU SN FN LN GR RC1 BY GR RC1||Show student number, first name, last name, grade, and race. Order by grade and race.|
|LIST STU SN FN LN GR IF GR = 12||Show student number, first name, last name, and grade if in twelfth grade.|
|LIST STU FN LN GR SX IF GR = 12 AND SX = M||Show first name, last name, grade, sex if in twelfth grade and male.|
|LIST STU FN LN GR SX IF GR = 10 OR GR = 12||Show first name, last name, grade, sex if in tenth or twelfth grade.|
|LIST STU FN LN GR SX IF GR > 10||Show first name, last name, grade, sex if in grades greater than tenth grade.|
|LIST STU FN LN GR SX IF GR < 11||Show first name, last name, grade, sex, if in grades less than eleventh grade.|
|LIST STU FN LN GR SX IF GR # 10||Show first name, last name, grade, sex, for all grades EXCEPT tenth grade.|
|LIST STU FN LN GR SX IF LN : SON
||Show first name, last name, grade and sex if the last name contains SON anywhere within it.|
|LIST STU FN LN GR PG IF PG ; LN||Show first name, last name, grade, parent/guardian if the parent/guardian name does not contain student's last name.|
Using Query to Proof your Work
|LIST STU SN FN LN RC1 BY RC1
||Will display students in order of race codes with blank race codes at the top of the list.|
|LIST STU SN FN LN IF CU = 0
||Will 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 - Will show total number of active students for each teacher. Secondary setting - Will show total student distribution across the counseling staff.|
|TOTAL STU RC1 GR BY RC1 GR||Will show total number of active students in order by race code and grade.|
|LIST STU SN FN LN ETH RC1 IF ETH = " "
AND RC1 = " "
|Will display Students that have blanks in the Ethnicity Indicator flag and Race field.|
Additional Data to Query in Aeries
|LIST STU SN FN LN NS NG NT||Will display Students next school, next grade and next teacher information.|
|LIST STU SN FN LN SX GR BD CU BY BD CU||Will display Students birthday sorted in order by birthday and teacher/counselor.|
|LIST TCH TN ID TE TT EM RM
||Will display specific Teacher information.|
|LIST CRS CN CO NA S1 DE LO HI E1||Will display specific Course information.|
|LIST COL ID NM AD CY ST ZC TL CD ZX AT||Will display specific College information.|
|LIST LOC CD NM AD CY ST ZC AC TL LO HI||Will display specific information for all Schools in the district.|
|LIST LKR LK CM SN C1 C2 C3 C4 C5 SE||Will display specific Locker information.|
|LIST EMP CD NM AD CY ST ZC ZX TL||Will display Employers set up for Work Permits.|
|LIST REQ CD SA C1 C2 C3 C4 GRT||Will display the Graduation Requirement information.|
|LIST CTL ID PT NM A1 GR BY ID PT||Will display all Tests set up and will be sorted in order by the Test ID and Part number.|