- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Some simple tips on how to count things in Voyager Prepackaged Access Reports.
There are 2 basic kinds of Access queries: list-of-stuff kinds of queries and calculate-stuff kinds of queries.
Counting things are calculate-stuff queries.
Simply put, counting things counts rows.
For example: Let's say we want to know how many barcodes each patron has? We know that patrons can have multiple barcodes. It can get quite complicated. Here's our initial query:
We a use a simple join on the PATRON and PATRON_BARCODE tables. We need to join them by PATRON_ID. After pulling down the three columns we need, we click on the Sigma Tool (a/k/a "Totals") .
A new row is created labeled "Total".
Each column is filled in by Group By.
Since we want to Count the barcodes, we select the PATRON_BARCODE column and change it to "Count"
Then run the query. Here are the results:
We can compare your results with the client to check if this is correct or not.
Note that the FIELD we choose to count does not define what we are counting!
It usually doesn't matter which field you choose to count because we are counting ROWS and not Fields. The exception to this rule is when the field has a value of NULL. When this happens it can mess up your results.
And so, it is usually best to choose to count one of the _ID fields because they are never NULL (unless you do some sort of extremely complex outer joins).
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: 21-MAY-2021