Query Design 1

Overview

Anyone using Axiom needs to have a basic understanding of query design. 

Overview of Query Design

After watching this video:

1. Describe the three parts of the query design screen.
2. Open a Find People query
   • Add sorting to three fields and then remove two of the sorts.
   • Rearrange the order of the fields.
   • Hide three fields.
3. Search for people whose last names begin with "Ab."

Saving Queries to Workspaces

You need to know how to save queries.

After watching this video, using your database:

1. Create yourself a subworkspace in your personal workspace called "Onboarding Training." This will be where you save all your queries during your onboarding training. Speak with your school's Veracross point person if you do not have your own workspace. 
2. Run (or re-run) the "Ab..." query you created in the first lesson and save it to your new "Onboarding Training" workspace. Name it, "My First Query." 

Query Folders

After watching this video:

1. What are query folders? How do you find and then add fields to the query?
2. How can you reset a query?
3. On the Find People query
   • update the Role field to "Student" 
   • update the Full Name field to "begins with" "Ab" (to limit the results) 
   • add fields pertaining to Parent 1 and Parent 2
   • save the query to your onboarding workspace and name it "Student Info"

Query Fields

After watching this video:

1. Head to the Find Students query on the Main homepage.
   • Hide the Room #, Dorm, and Enrollment Status fields.
   • Select the "Rearrange all fields by display status" on the gear icon.
   • Sort by Current Grade and then Full Name. Run the query.
   • Preview of coming attractions: Back in the design view, subtotal the query by Current Grade. To do this, click the little "Subtotal Header" button for Current Grade (it's on the right beneath the criteria). Run the query. Cool, right?
   • Save the query as "Students by Grade Level". 

Basic Criteria

After watching this video:

1. Throughout Veracross, what does the Full Name field mean?
2. What is an operator? What is the default operator for text the Full Name field?
3. Define the following operators on text fields: is, is not, is blank, is not blank, begins with, contains, does not contain. Give a real world use case for each (e.g., "Use 'is blank' on the Preferred Name field to find anyone who does not have a preferred name in the database."). 
4. What is the shortcut to force the operator to "contains"? Use it to run a Find People query for anyone whose last name contains "John."
5. Run a Find People query where their Role field is any of the "Parent..." options.
6. Reset the Find People query. How many people do not have an Email 2? (hint: you'll need to pull in the Email 2 field and then think about which operator will give you the result you want). Save the query to your workspace as "People Without Email 2."

Totals and Subtotals

After watching this video:

1. What are totals and subtotals in queries? What are the various options? How do you add and remove totals and subtotals?
2. In the off-chance you aren't sure what a field means, is there any help text describing it?
3. The upper school division head accosts you in the hallway and declares, "I need to see the average of 10th graders' report card grades. Can you do that?" See if you can create and save this query. Name it "10th Graders Avg Posted Grades {grading period}." If you don't have a Grade 10 at your school, substitute it for a Grade Level that you do have. Bonus: try subtotaling by class.
4. The tennis coach serves up an ace and then runs over to you, saying that he needs to see how his team has been doing this year. He thinks he's recorded some (but not all) of the match results, and he'd love to just see what we've got. If you do not have the Athletic module, use the Demo Database. If you are using your own database, you will likely need to modify this a bit.
   • Head to the Athletic homepage, Find Athletic Events query.
   • Pick the appropriate event types (i.e., exclude practices, etc.).
   • Select "US Coed Tennis" for the sport.
   • Pull in the "Score - Us" and "Score - Opponent" fields.

Summarizing and Subtotaling

After watching this video:

1. What does it mean to use a subtotal header? When would it be useful?
2. What about summarizing? When would that be useful?
3. Create a query showing admission candidates subtotaled and sorted by Grade Applying For (GAF).
4. Return to the design view of the query and summarize by the GAF field.

Query Results: Actions

After watching this video:

1. What actions can you take with query results?
2. Run a Find Classes query and detail the steps for batch updating the Teacher field on 5-10 classes. 
3. Run the Review Class Configuration query and then filter to just sixth grade classes and explain the process of batch updating the Parent Portal Assignment Display field to "Assignments Only." (hint: if you can't find the query, remember that you can type the name in the search bar)
4. How do you undo a batch update or delete operation?
5. Find all Spanish classes, subtotaled and sorted by teacher. Export this to Excel and also print it to PDF. 
6. What field needs to be displayed in a query in order to send an email to the people listed in the results?
7. Run a Find People query and arbitrarily limit the results. Start the process of sending them an email.