Query Training: Academics and Student Life

Guided Query Practice

Try answering these questions by reading the prompt and working following the steps to create a query that gets you the results you are looking for. After you’ve completed these exercises, or if you are stuck on any of them, feel free to navigate to the guided query answers here.

  1. “I wonder how many people’s names start with “Ab”? you mused. Name the query “Last Names Starting With Ab”
    • create a query by starting from a Find Person query on the Main Homepage
    • anyone with a last name beginning with “Ab”
    • sort by last name (descending)
  2. Find students for the registrar. Name the query “High School Students Advisors, Homeroom Teachers”
    • create a query by starting on a Find Students query on the Main homepage
    • students in high school, subtotaled by grade
    • fields to include: Person ID, Full Name, Current Grade, Advisor, Homeroom Teacher, Gender, Roles
    • sort by grade and then last name
  3. Find middle school classes for the registrar. Name the query “Current MS Classes by Grade and Teacher.”
    • create a query from the Find Classes query on the Middle School homepage
    • taught in the current school year, active or future status
    • subtotal by grade and then teacher
    • sort by grade, then teacher, then class ID
  4. Find lower school class enrollments by student for the registrar. Name the query “Lower School Classes by Student.”
    • create a query from the Find Classes query on the Lower School homepage
    • subtotal by student
    • sort by student, then class ID
    • hide the Late Date Enrolled, and Date Withdrawn fields
    • hide the field(s) you pulled in to limit the search to lower school
    • rename the Status field to “Class Status”
    • ignore enrollments with a Date Withdrawn populated
  5. Thus spake the registrar, “I need to know who took a religion class last year.”
    • create a query from the Find Class Enrollment Records query on the System homepage
    • pull in the Subject field and set the criteria as “is one of” US Religion
    • set the School Year as the Previous School Year
    • save the query and name it “Last Year’s Religion Classes.”
  6. Thus spake the upper school division head, “I need to see how kids are doing this grading period in their classes.”
    • Create a query (start with “Find Class Assignment Grades”) that shows an average grade of class assignments for upper school classes in a given grading period (arbitrary).
    • Name the query, “Class Assignment Grade Averages in {grading period}”
  7. “I want to see my AP European History class roster, including those kids who dropped,” Debbie casually mentioned to you in passing. Pick a different class if you are using your own database.
    • Head to the class detail screen (class ID: APEuro.8).
    • Pop out the roster and adjust the query to include withdrawn kids. (hint: look at any criteria in the query that may prevent a withdrawn student from being shown).
    • Save the query, naming it “APEuro.8 All Class Enrollments for Debbie”
  8. All of the upper school classes need to be hidden from the parent and student portals. Do not complete this one if you are using your own database! 
    • Head to the Review Class Configuration query and batch update the Parent Portal Visibility and Student Portal Visibility fields to “Hide.”
  9. Pick one of the amazing queries you created that doesn’t have a gazillion records in the result.
    • Print the results to a PDF.
  10. Pick another one of your amazing queries.
    • Adjust any search criterion (e.g., change the second query to search for MS instead of HS students).
    • Save it as a new query with an appropriate new name.
  11. Bonus: Can you create a chart?
    • Head to the Admissions homepage and run the “By Grade” pipeline report.
    • Create a Cartesian chart.
    • Dimension: grade level.
    • Add three or four series to the chart to measure:
      • in-process applications
      • completed applications
      • LYTD applications
    • Flip the dimension and measure axes.

Independent Query Practice

Now that you’ve had some practice building queries, attempt to build the following queries on your own. After you’ve completed this, check your answers here.

  1. “I need a list of students who had a master attendance status of “Absent – Unexcused” any day last month.”
  2. “Please show me the Upper School classes that have at least 15 students enrolled in them.”
  3. “How many Boarding students do we have this year?”
  4. “Can you get me a list of all students who have lockers numbers between 510 and 700?”

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Guided Query Answers

  1. “I wonder how many people’s names start with “Ab”? you mused. Name the query “Last Names Starting With Ab”
  2. Find students for the registrar. Name the query “High School Students Advisors, Homeroom Teachers”
    • students in high school, subtotaled by grade
    • fields to include: Person ID, Full Name, Current Grade, Advisor, Homeroom Teacher, Gender, Roles
    • sort by grade and then last name
    • https://axiom.veracross.com/#/results/399054
  3. Find middle school classes for the registrar. Name the query “Current MS Classes by Grade and Teacher”
  4. Find lower school class enrollments by student for the registrar. Name the query “Lower School Classes by Student.”
    • subtotal by student
    • sort by student, then class ID
    • hide the Late Date Enrolled, and Date Withdrawn fields
    • hide the field(s) you pulled in to limit the search to lower school
    • rename the Status field to “Class Status”
    • ignore enrollments with a Date Withdrawn populated
    • https://axiom.veracross.com/#/query/399061
  5. Thus spake the registrar, “I need to know who took a religion class last year.”
    • create a query from the FInd Class Enrollment Records query on the System homepage
    • pull in the Subject field and set the criteria as “is one of” US Religion
    • set the School Year as the Previous School Year
    • save the query and name it “Last Year’s Religion Classes.”
    • https://axiom.veracross.com/#/query/399063
  6. Thus spake the upper school division head, “I need to see how kids are doing this grading period in their classes.”
    • Create a query (start with “Find Class Assignment Grades”) that shows an average grade of class assignments for upper school classes in a given grading period (arbitrary).
    • Name the query, “Class Assignment Grade Averages in {grading period}”
    • https://axiom.veracross.com/#/query/399068
  7. “I want to see my AP European History class roster, including those kids who dropped,” Debbie casually mentioned to you in passing.
    • Head to the class detail screen (class ID: APEuro.8).
    • Pop out the roster and adjust the query to include withdrawn kids. (hint: look at any criteria in the query that may prevent a withdrawn student from being shown).
    • Save the query, naming it “APEuro.8 All Class Enrollments for Debbie”
    • https://axiom.veracross.com/#/query/399088
  8. All of the upper school classes need to be hidden from the parent and student portals.
    • Head to the Review Class Configuration query and batch update the Parent Portal Visibility and Student Portal Visibility fields to “Hide.”
  9. Pick one of the amazing queries you created that doesn’t have a gazillion records in the result.
    • Print the results to a PDF.
  10. Pick another one of your amazing queries.
    • Adjust any search criterion (e.g., change the second query to search for MS instead of HS students).
    • Save it as a new query with an appropriate new name.

Independent Query Answers

  1. “I need a list of students who had a master attendance status of “Absent – Unexcused” any day last month.”
    • start from the Find Master Attendance query on the Attendance homepage
    • set the “Status (students)” criteria as “is one of” Absent – Unexcused
    • set the attendance date to “date is between” the first day of last month and the last day of last month
    • subtotal or summarize by student, depending on how you want the information to display
    • https://axiom.veracross.com/#/query/399095
  2. “Please show me how many Upper School classes have at least 15 students enrolled in them.”
  3. “How many Boarding students do we have this year?”
  4. “Can you get me a list of all students who have lockers numbers between 510 and 700?”
print

Related Articles