Business Query Practice

Updated Jul 13, 2021
Published 07/01/2019

As of July 12, 2021, this "Learn Veracross" site has been deprecated.  It will remain live through December 2022, but will no longer be updated. All knowledge content has moved to the new Veracross Community.  Please update your bookmarks.

Here is the new version of this article in the Veracross Community.

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 below.

  1. “Hello. Could you build me a summarized query that shows me how many students are in each grade level?”
    • start from the Find Students query of the Main homepage
    • pull in the Grade Level field and turn on the Summarize field role option
  2. “Could you please get me a listing of unrestricted asset and liability accounts, grouped by whether they are an asset or liability?”
    • start from the top GL Chart of Accounts query on the General Ledger homepage
    • set the criteria for the Restriction type field to be “is one of” and check the Unrestricted box
    • set the criteria for the Account Type 2 field to be “is one of” and check the Asset and Liability boxes
    • subtotal by the Account Type 2 field
  3. “Would you be able to get a list of all of our comprehensive invoices that were created for grades 9-12 of the most recent school year?”
    • start from the AR Invoice Header Listing query on the AR Workflow homepage
    • set the School Year criteria to be “is one of” and check off the most recent school year option
    • set the Invoice Type criteria to be “is one of” and check off the comprehensive option
    • pull in the Grade Level field and set the criteria as “is one of” and check off the Grade 9, Grade 10, Grade 11, and Grade 12 boxes
  4. “Can you please provide me with a list of all unpaid balances over $500?”
    • start from the AR Invoice Header Listing query on the AR Workflow homepage
    • set the criteria for the Unpaid Amount field to be “is greater than” 500
  5. “Would you show me all households with a credit on their account?”
    • start from the AR Invoice Header Listing query on the AR Workflow homepage
    • set the Unpaid Amount field’s criteria to be “is less than” 0
    • set the criteria for the Household field as “is not blank”
  6. “Show me all Deposits for all households beginning with A…”
    • start from the Cash Deposit Report query on the Cash Processing homepage
    • pull in the Household field and set the criteria to be “begins with” A
  7. “Could you get me a listing of all accounts receivable invoice items that were due on or before today, and then group them by whether or not they’re posted?”
    • start from the AR Invoice Item Listing query on the AR Workflow homepage
    • set the criteria for the Due Date field to “is on or before” and type in {today}
    • subtotal by the “Posted?” field
  8. “Please find me all household invoices with a one-off payment frequency.”
    • start from the AR Invoice Header Listing query on the AR Workflow homepage
    • pull in the Payment Frequency field and set the criteria to “is one of” One Installment
  9. “Would you be able to provide us a query showing all the revenue general ledger accounts?”
    • start from the top GL Chart of Accounts query on the General Ledger homepage
    • set the criteria for the Account Type 2 field to be “is one of” and check the Revenue box
  10. “Show me all AP invoices with a posted amount of greater than $100 but less than $1000.”
    • start from the AP Invoice Header Listing query on the Accounts Payable homepage
    • set the criteria on the As Posted Amount field as “is between” 100 and 1000, respectively

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. “Please provide me with a query that shows all of our assets that are non-depreciable.”
  2. “Something isn’t adding up correctly. Could you show me all simple receipts that have a receipt date between 5/1/2019 and 5/31/2019?”
  3. “Would you be able to pull up all cash receipt batches that have a batch total of greater than $10,000?”
  4. “I’m curious how many vendors we have that actually supplied their fax numbers. Could you query that for me?”

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Guided Query Answers

  1. “Hello. Could you build me a summarized query that shows me how many students are in each grade level?”
  2. “Could you please get me a listing of unrestricted asset and liability accounts, grouped by whether they are an asset or liability?”
    • start from the top GL Chart of Accounts query on the General Ledger homepage
    • set the criteria for the Restriction type field to be “is one of” and check the Unrestricted box
    • set the criteria for the Account Type 2 field to be “is one of” and check the Asset and Liability boxes
    • subtotal by the Account Type 2 field
    • https://accounting.veracross.com/#/query/41225
  3. “Would you be able to get a list of all of our comprehensive invoices that were created for grades 9-12 of the most recent school year?”
    • start from the AR Invoice Header Listing query on the AR Workflow homepage
    • set the School Year criteria to be “is one of” and check off the most recent school year option
    • set the Invoice Type criteria to be “is one of” and check off the comprehensive option
    • pull in the Grade Level field and set the criteria as “is one of” and check off the Grade 9, Grade 10, Grade 11, and Grade 12 boxes
    • https://accounting.veracross.com/#/query/41228
  4. “Can you please provide me with a list of all unpaid balances over $500?”
  5. “Would you show me all households with a credit on their account?”
    • start from the AR Invoice Header Listing query on the AR Workflow homepage
    • set the Unpaid Amount field’s criteria to be “is less than” 0
    • set the criteria for the Household field as “is not blank”
    • https://accounting.veracross.com/#/query/41231
  6. “Show me all Deposits for all households beginning with A…”
  7. “Could you get me a listing of all accounts receivable invoice items that were due on or before today, and then group them by whether or not they’re posted?”
    • start from the AR Invoice Item Listing query on the AR Workflow homepage
    • set the criteria for the Due Date field to “is on or before” and type in {today}
    • subtotal by the “Posted?” field
    • https://accounting.veracross.com/#/query/41260
  8. “Please find me all household invoices with a one-off payment frequency.”
  9. “Would you be able to provide us a query showing all the revenue general ledger accounts?”
  10. “Show me all AP invoices with a posted amount of greater than $100 but less than $1000.”

Independent Query Answers

  1. “Please provide me with a query that shows all of our assets that are non-depreciable.”
  2. “Something isn’t adding up correctly. Could you show me all simple receipts that have a receipt date between 5/1/2019 and 5/31/2019?”
  3. “Would you be able to pull up all cash receipt batches that have a batch total of greater than $10,000?”
  4. “I’m curious how many vendors we have that actually supplied their fax numbers. Could you query that for me?”
Copyright © 2019 Veracross. All rights reserved.