Question

Some simple tips on how to count things in Voyager Prepackaged Access Reports.

Answer

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).

• Article last edited: 21-MAY-2021