- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
How to read a query and understand what it does in Voyager Prepackaged Access Reports.
Prepackaged Access Reports comes with many queries "out of the box".
Some queries have descriptions in Access. You can find them by right-clicking on the query name and going to "Object Properties."
Use the Data Dictionary for a list of all tables and columns. This document is useful for both reading and writing queries.
Four things to look at when reading a query in the Design View:
When you open a query, you can drag down the bottom border of the upper pane for better viewing. Keep in mind that if you make changes to a query, you will be asked if you want to save those changes. It is advisable to make a copy rather than alter an original OOTB query.
Examine what tables are used. For example, in "Fine and Fee Transactions" there are a lot of tables! Note any tables that have criteria (see below).
Examine the criteria. in "Fine and Fee Transactions" there is only one: transaction date. The square brackets are for parameters, for which you will be prompted when you run the query.
Examine the columns. The Data Dictionary mentioned above can help. Note that some columns may be renamed (in "Fine and Fee Transactions", for example, the left-most column is renamed).
Finally, examine any sorting ("Fine and Fee Transactions" has no sorting).
In another example, "Item Status - In Process" you will find one criteria and a primary and secondary sort (Note that Access sorts from left to right).
Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes. Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.
- Article last edited: 11-Mar-2021