Review of Query Commands


A Query Command is the first step used when generating a query.   The structure is a definite order that the query must be entered when being created.  The following are the Commands:

  • LIST – will display the data selected from tables which are required but fields, sorts and conditions are optional
  • TOTAL - will calculate a total amount but must contain a sort on all fields in the same order as listed 
  • SKIP - will bypass specific records
  • KEEP - will select specific records
  • CHANGE - will make permanent changes to an existing record

            (Only if user has permissions)


The following are the structures used for each Command:


LIST 
Command: Table(s) – Field(s) – Sort(s) (BY) – Condition(s) (IF)

LIST STU MED STU.NM MED.CD?

LIST MST CRS TCH TCH.TE MST.RM MST.SM MST.PD CRS.CO MST.MX MST.TS BY TCH.TE MST.PD MST.SM IF CRS.CO # TEACH
TOTAL
Command: Table(s) - Field(s) - Sort(s) (on ALL fields in order) - Condition(s) (IF)

TOTAL STU GR SX BY GR SX

TOTAL STU ADS STU.GR ADS.CD BY STU.GR ADS.CD IF ADS.DS : SUS
SKIP
Command – Table(s) (Start with STU) – Condition(s) (IF)

SKIP STU IF GR > 4

SKIP STU CSE IF CSE.DI > 0
KEEP
Command – Table(s) (Start with STU) – Condition(s) (IF)

KEEP STU CSE IF CSE.DI # " "

KEEP STU TST IF TST.PL < 3 AND TST.PL > 0
CHANGE
Command – Table(s) – Field (being copied to) “TO” Field (being copied from)

CHANGE STU PED TO 15 IF PED = " "

CHANGE STU SSD SEC MST CRS TCH SSD.TGC TO MST.TN IF CRS.DC = E



NOTE: If using KEEP/SKIP a reset MUST be performed after you have completed working with the limited data or data on all screens will be limited to the query generated. Also, there is NO UNDO button for the CHANGE command.




Sorting Data – Ascending or Descending Order


When a LIST query is generated the program determines which field will be used to sort in ascending order.  For example, LIST STU is sorted in ascending order by student last name. 


When creating a query the resulting data can be sorted in a different order, such as by grade.  The order can be selected in ascending order or descending order.  Descending order will reverse the order selected to sort.  To descend a query statement you must either use DESC, REV, or ^ symbol after the chosen field in the BY portion of the query.


  • If more than one sort field is selected the firstsort field entered will be used to determine the page break in Aeries formatted reports.
    • LIST STU ADS STU.SN STU.NM STU.GR STU.SX ADS.CD? ADS.DS BY STU.GR STU.SX
    • LIST STU SEC MST TCH CRS MST.SM MST.PD STU.SN STU.NM TCH.TE CRS.CO BY TCH.TE MST.PD IF MST.SM = F OR MST.SM = Y
  • A query sort using descending order has REVadded after the sort field.
    • LIST STU ADS STU.SN STU.NM STU.GR STU.SX ADS.CD ADS.CD? ADS.DS BY STU.GR REV IF ADS.CD # " "
  • A sort on more than one field can also be set up to descend with one field and ascend with another.
    • LIST STU ADS STU.SN STU.NM STU.GR STU.SX ADS.CD ADS.CD? ADS.DS BY STU.GR REV STU.SX IF ADS.CD # " "




Aeries Query Symbols versus Access / SQL Symbols


The Aeries Client program uses Aeries Query symbols in the Query program and Access Query symbols can be used throughout various forms and reports.  The following are the different Aeries and Access /SQL symbols that are utilized.   


AERIES SYMBOLS

ACCESS / SQL SYMBOLS

Recognizes text fields
 
# Is NOT EQUAL to
 
: CONTAINS or is Like
 
; Does NOT CONTAIN
 
< LESS THAN,   > GREATER THAN
 
 
 
+ ADD,   - SUBTRACT
((  START MATH,   )) STOP MATH
 
LEFT(field,1) chooses 1 char from left side 
 
RIGHT(field,4) chooses 4 char from right side
Text fields must be enclosed in quotes
 
<> Is NOT EQUAL to
 
LIKE (“___”), where ___ is the text
 
NOT LIKE (“___”),  where ___ is the text
 
<  LESS THAN,   > GREATER THAN
<=  LESS THAN OR EQUAL TO
>=  GREATER THAN OR EQUAL TO
 
+  ADD,  -  SUBT,  /  DIV,  * MULT
 
 
LEFT(field,1) chooses 1 char from left side 
 
RIGHT(field,4) chooses 4 char from right side



Some Aeries programs will display a selection box allowing you to enter an Access Query IF statement. The Query IF selection only uses Access symbols not Aeries symbols.



The following are Access Query examples that could be utilized on Class Roster Setup:

 

TN <> 605 - Print all rosters except teacher number 605

TE LIKE "A*" - Print rosters for all teachers whose last name starts with “A”

TE NOT LIKE "A*" - Print rosters for all teachers whose last names does NOT start with “A”.

AD LIKE "*Alliance*" - In Student Directory by Name for all student’s that have Alliance in their Address.