- 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).
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).
Finally, note that there are different kinds of queries.
Query types are separated into "sections" in the Access Navigation Pain, and identified by their icon. For example:
These represent normal select queries and crosstab queries. They put results into Datasheet View when you run them.
This is a union query. They display at the bottom of the Navigation Pane. Running this puts results into Datasheet View. A union query cannot be displayed in Design View, only in SQL View. This is because a union query takes two tables with the same fields and makes one longer table out of them.
These represent Make Table queries. They do not put results into Datasheet View. Append and Update queries change made tables.
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