What are Union Queries in Voyager Prepackaged Access Reports?
- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Question
What are Union Queries in Voyager Prepackaged Access Reports?
Answer
The Standard "Inner" and Special "Outer" Joins that we use in Access imply a "horizontal" relationship between two tables. Tables in Union Queries imply more of a "vertical" relationship.
A union query takes two (or more!) tables with the same fields and makes one longer table out of them.
An out-of-the-box example in your Reports.MDB file is: "Circulation Transactions (Charges)". Take a look at it.
You'll note that this query displays in a section at the bottom of your Queries Navigation Pane, and that it has a different sort of icon that looks like this:
The "Circulation Transactions (Charges)" Union Query takes two tables with the same fields and makes one longer table out of them.
In Access, Union Queries are SQL-Only queries, and can't be displayed in "Design View".
The two tables (or the fields you select from them) have the exact same fields.
Example (Charge-only for items returned and Renewal Circulation Statistics):
SELECT LOCATION_CODE, PATRON_GROUP_CODE, CHARGE_DATE AS TRANS_DATE
FROM (PATRON_GROUP INNER JOIN CIRC_TRANS_ARCHIVE ON
PATRON_GROUP.PATRON_GROUP_ID = CIRC_TRANS_ARCHIVE.PATRON_GROUP_ID)
INNER JOIN LOCATION ON CIRC_TRANS_ARCHIVE.CHARGE_LOCATION =
LOCATION.LOCATION_ID
UNION
SELECT LOCATION_CODE, PATRON_GROUP_CODE, RENEW_DATE AS TRANS_DATE
FROM ((RENEW_TRANS_ARCHIVE INNER JOIN CIRC_TRANS_ARCHIVE ON
RENEW_TRANS_ARCHIVE.CIRC_TRANSACTION_ID =
CIRC_TRANS_ARCHIVE.CIRC_TRANSACTION_ID) INNER JOIN PATRON_GROUP ON
CIRC_TRANS_ARCHIVE.PATRON_GROUP_ID = PATRON_GROUP.PATRON_GROUP_ID)
INNER JOIN LOCATION ON RENEW_TRANS_ARCHIVE.RENEW_LOCATION =
LOCATION.LOCATION_ID;
Example (For all Circulation Statistics):
SELECT LOCATION_CODE, PATRON_GROUP_CODE, CHARGE_DATE AS TRANS_DATE
FROM (PATRON_GROUP INNER JOIN CIRC_TRANS_ARCHIVE ON PATRON_GROUP.PATRON_GROUP_ID
= CIRC_TRANS_ARCHIVE.PATRON_GROUP_ID) INNER
JOIN LOCATION ON CIRC_TRANS_ARCHIVE.CHARGE_LOCATION = LOCATION.LOCATION_ID
UNION
SELECT LOCATION_CODE, PATRON_GROUP_CODE, RENEW_DATE AS TRANS_DATE
FROM ((RENEW_TRANS_ARCHIVE INNER JOIN CIRC_TRANS_ARCHIVE ON
RENEW_TRANS_ARCHIVE.CIRC_TRANSACTION_ID =
CIRC_TRANS_ARCHIVE.CIRC_TRANSACTION_ID) INNER JOIN PATRON_GROUP ON
CIRC_TRANS_ARCHIVE.PATRON_GROUP_ID = PATRON_GROUP.PATRON_GROUP_ID) INNER JOIN
LOCATION ON RENEW_TRANS_ARCHIVE.RENEW_LOCATION = LOCATION.LOCATION_ID
UNION
SELECT LOCATION_CODE, PATRON_GROUP_CODE, CHARGE_DATE AS TRANS_DATE
FROM (PATRON_GROUP INNER JOIN CIRC_TRANSACTIONS ON PATRON_GROUP.PATRON_GROUP_ID =
CIRC_TRANSACTIONS.PATRON_GROUP_ID) INNER JOIN LOCATION ON
CIRC_TRANSACTIONS.CHARGE_LOCATION = LOCATION.LOCATION_ID
UNION
SELECT LOCATION_CODE, PATRON_GROUP_CODE, RENEW_DATE AS TRANS_DATE
FROM ((RENEW_TRANSACTIONS INNER JOIN CIRC_TRANSACTIONS ON
RENEW_TRANSACTIONS.CIRC_TRANSACTION_ID = CIRC_TRANSACTIONS.CIRC_TRANSACTION_ID)
INNER JOIN PATRON_GROUP ON CIRC_TRANSACTIONS.PATRON_GROUP_ID =
PATRON_GROUP.PATRON_GROUP_ID) INNER JOIN LOCATION ON
RENEW_TRANSACTIONS.RENEW_LOCATION = LOCATION.LOCATION_ID;
Be aware of the difference between the two "Union" SQL Keywords:
UNION Combines two tables into one long table
UNION ALL Does a UNION, but does not delete duplicate records (this can be a “gotcha” when looking at, for example, circulation data)
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: 03-Mar-2021