Subqueries in Voyager Prepackaged Access Reports
- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Question
Example of using a subquery in a Prepackaged Access Reports query.
Answer
Subqueries fill the same needs in Prepackaged Access Reports as Make Table queries, but in a different way. You usually use them when you want to write a query, but one of the tables you need does not exist.
But unlike Make Table queries, subqueries don't save the intermediate results in your Access database, and therefore don't make your Access database much (sometimes very much!) larger.
Subquery example:
Let's say you want to find all Patrons with duplicate barcodes. In other words, barcodes that appear more than once in the database.
First, write the subquery (we're using the Sigma Tool to get a "Total" row):
Save this query with a name like "Dup patron barcodes subquery":
You can run your subquery to check the results. This one will list all the patrons that have duplicate barcodes (in other words, all the patron barcodes that appear more than once in the database):
|
Patron_Barcode |
Num_Occurrences |
|
20999000012300 |
2 |
|
20999000076500 |
3 |
|
20999000034500 |
2 |
Second, write the main query. When you are selecting tables for the main query, click on the Queries tab and select your "Dup patron barcodes subquery"
Then click the Tables tab and add your other tables:
Add your links, save the query and run it:
Additional Information
Remember that you can't have two objects with the same name, so it is wise before you start with subqueries to come up with a naming convention to help you remember what are main queries versus subqueries. Support recommends you put “Main query” in the name of main queries as a reminder that there’s a subquery.
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: 24-Jun-2021