Multi-table queries, the "CHANGE" button and command; advanced applications of Query for secondary school personnel.
- Tips for complex queries
- Advanced understanding of Aeries Tables Relations
- Defining Multiple Tables & fields in a query
- Use of the Database Table Information Report or Web Fields Doc
- Building complex Query Statements
- Sorting Data - Ascending or Descending Order
- Aeries Query Commands vs. Access Query Commands
- Selecting Records by Criteria “IF”
- Using Connectives “AND” and “OR”
- Math Operations within Query
- Undo Last KEEP/SKIP
- Change Button versus Change Command
- Change Button with Multiple Tables
- Advanced use of the Query Letter Editor
- Creating & Customizing Advanced Query Labels or Envelopes
- Troubleshooting of Query Errors
Query Principles and a Review of Basics
- Identify the fields you wish to include and the tables from which they come.
- Using a KEEP or SKIP may make the final outcome easier or more accomplishable, but a full use of the IF portion is always best if you wish to save the query as a one-step solution.
- What is the required table order to make the query run with the desired outcome?
- Using sorts can make it easier for those that run it later?
- Do I need to teach someone else how to do this query? Share the load and make life easier for yourself and more rewarding for others.
The Query Form is Dynamically Helpful in Building Statements
The 5 function areas of a query statement are:
- Command [LIST TOTAL KEEP SKIP CHANGE]
- Field(s) (optional)
- Sort [BY] (optional)
- Conditions [IF] (optional)
NOTE: Not all are required but they are to be in this order if utilized
How do we know what tables to include and the order?
- Rely on the interactive nature of the Query Form starting with a primary first table.
- Review queries that resemble what you are trying to accomplish.
The following examples exist for the web version although some may work in the client for the time it is still relevant:
--Sometimes you need to set a value to a blank string ""
--This changes U9 to a space in the client
CHANGE STU U9 TO " "
--This changes it to a blank, zero length string in the web
CHANGE STU U9 TO (( '' ))
--Grad Year in a math function that you never have to change (web only)
LIST STU SC ID NM GR (( SUBSTRING(DB_NAME(),4,2) + 2001 + 12 - GR ))
--Gradebook term scores and percent (right column contains DTS of last term specific recalculation most likely caused by teacher intervention and will suffice as a rudimentary last teacher modification timestamp)
LIST STU GBV GBU GBK GBE MST TCH STU.SC STU.ID STU.NM STU.GR STU.TG? GBK.GN GBV.CD GBK.NM TCH.TE GBE.SE MST.CN GBV.MK GBV.PC (( CAST(GBV.DTS AS DATETIME) )) IF GBK.GN = GBV.GN AND GBK.SC = TCH.SC
--Gradebook term scores and percent if you wish to focus in on a specific day
LIST STU CAR GBV GBU GBK GBE MST TCH STU.SC STU.ID STU.NM STU.GR STU.TG? GBK.GN GBV.CD GBK.NM TCH.TE MST.PD GBE.SE MST.CN GBV.MK GBV.PC BY STU.ID GBV.CD MST.PD IF GBK.GN = GBV.GN AND GBK.SC = TCH.SC AND CAR.SE = GBE.SE AND CAR.DS <= "3/27/2018" AND CAR.DE >= "3/27/2018"
--Gradebook term scores and percent including assignment percentage when title is like “Final”
LIST STU GBS GBA GBV GBU GBK GBE MST TCH STU.SC STU.ID STU.NM STU.GR STU.TG? GBK.GN GBV.CD GBK.NM TCH.TE GBE.SE MST.CN GBV.MK GBV.PC GBA.DD GBA.AD GBS.PC IF GBK.GN = GBV.GN AND GBK.SC = TCH.SC AND GBA.AD : "FINAL"
--Gradebook Assignment tallies
TOTAL TCH GBK GBA GBR TCH.TE GBK.NM GBR.CD GBK.PD BY TCH.TE GBR.CD GBK.NM GBK.PD IF GBK.SC = TCH.SC AND GBA.DD >= GBR.SD AND GBA.DD <= GBR.ED
--Gradebook Missing Assignment
LIST STU GBU GBS GBA GBK GBR TCH STU.ID STU.NM GBK.GN GBR.CD GBK.NM TCH.TE GBU.CSC GBU.CMK GBA.AN GBA.AD GBA.DD GBA.GC GBS.MK IF GBA.GC = 1 AND GBS.MK = " " AND GBA.DD >= GBR.SD AND GBA.DD <= GBR.ED AND GBK.SC = TCH.SC
--Gradebook Comments Made on Scores
LIST TCH GBK GBA GBS STU TCH.SC TCH.TE GBK.NM GBA.AD STU.ID STU.NM GBS.MK GBS.CO IF GBS.CO > " "
--A Query to find Double quotes in first or last names...
LIST STU ID NM IF LN : "'+CHAR(34)+'" OR FN : "'+CHAR(34)+'"
Why does this query work in Client but not in Web?
LIST STU SC NM ID GR GP DE DA DP (( LEFT ( (( DP / DE * 100 )) , 5 ) + "%" )) BY NM (( DP / DE )) GP IF DE # 0 AND DA # 0
--SQL is the engine behind the Aeries web version and not MS Access.
--SQL does not presume the output to be a different precision than the elements being divided (integer divided by integer will cast output as an integer)
LIST STU ID NM (( 1 / 2 )) = 0 ?!?!
--If one of the elements in the division operation where a decimal the output could then be depicated as a decimal and give the value you may need
LIST STU ID NM (( 1 / 2.0 )) = 0.5
--Multiplying either element by 1.0 is harmless and will allow SQL to depict the final outcome as a decimal.
LIST STU ID NM (( 1.0 * 1 / 2 )) = 0.500
--This does attendance percentage in Aeries Query in the web by including a decimal multiplication before division and will allow the output to be cast as a decimal.
LIST STU NM DE DA DP (( 1.0 * 100 * DP/DE )) IF DE # 0
--Use of the STU.DE/DA/DP fields is unnecessary recalculation work. Use this Aeries query in Web after enabling Attendance History recalculation nightly.
LIST STU AHS STU.NM AHS.EN AHS.AB AHS.PR (( 1.0 * 100.0 * AHS.PR/AHS.EN )) IF AHS.EN # 0 AND AHS.YR = "2016-2017"
--Or a more complicated example rounding to two decimal places and concatenating a ‘%’ on the end of it…
LIST STU AHS STU.LN STU.FN STU.GR AHS.YR AHS.EN AHS.PR (( CAST( CAST( ROUND ( 1.0 * 100 * AHS.PR / AHS.EN ,2 ) AS DEC(20,2) ) AS VARCHAR(21) ) + "%" )) BY (( 1.0 * 100 * AHS.PR / AHS.EN )) IF AHS.EN # 0 AND AHS.YR = "2017-2018"
--An Admin Account can query the LOG table to investigate modifications of data
LIST LOG IF CD : "CON"
--Including a math function of (( DTS )) will list the Date Time Stamp
LIST LOG TID DT SC SN ID USR RCD OD ND (( DTS )) IF CD : "CON"
--An Admin Account can also query the email log table (EML)
LIST EML EID TN UN SCL DT FAD TAD SUB (( DTS )) IF TAD : "email@example.com"
--Columns that are already a date time may be depicted as a date when you also need the time. Try the following when querying ASL
LIST ASL SC TN DT P0 P1 P2 P3 P4 P5 P5 P7 P8 P9 (( CAST(T0 AS DATETIME) )) (( CAST(T1 AS DATETIME) )) (( CAST(T2 AS DATETIME) )) (( CAST(T3 AS DATETIME) )) (( CAST(T4 AS DATETIME) )) (( CAST(T5 AS DATETIME) )) (( CAST(T6 AS DATETIME) )) (( CAST(T7 AS DATETIME) )) (( CAST(T8 AS DATETIME) )) (( CAST(T9 AS DATETIME) ))
--Total D's or F's for GRD Mark 1
TOTAL STU GRD STU.FN STU.LN GRD.M1 BY STU.FN STU.LN GRD.M1 IF GRD.M1 : "D" OR GRD.M1 : "F"
--Listing Program involvement isn’t easy as you will only see those that do have it or those with no other PGM records at all with the following two queries.
LIST STU PG STU.SC STU.ID STU.NM STU.GR STU.SP STU.SP? PGM.CD? IF PGM.CD = "135" OR PGM.CD = NULL
--Trying to get very precise still fails to list all students
LIST STU PG STU.SC STU.ID STU.NM STU.GR STU.SP STU.SP? PGM.CD? IF ( PGM.CD = "135" OR PGM.CD = NULL ) AND ( ( PGM.PSD <= "3/7/2017" AND ( PGM.PED >= "3/7/2017" OR PGM.PED = NULL ) ) OR ( PGM.ESD <= "3/7/2017" AND ( PGM.EED >= "3/7/2017" OR PGM.EED = NULL ) ) )
Using the Custom Report Builder is a far easier way to output program involvement and other sub groups.
Analytics is another friendlier way to dig in to data. You can drill down by any other sub group far easier than having to be adept at a query language.
Since people still had an interest in having a lot of relational data depicted on one line, I have made SQL queries to do that. The future may allow for the easy execution of store SQL Queries, Views, or maybe even Stored Procedures from Aeries Query.
Here's a link to that SQL Query...