Difference between the "CIRC_TRANSACTIONS" and "CIRC_TRANS_ARCHIVE" tables in Voyager PrePackaged Reports
- Product: Voyager
- Product Version: 7
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Question
What is the difference between the CIRC_TRANSACTIONS and CIRC_TRANS_ARCHIVE tables in Voyager PrePackaged Reports?
Answer
- CIRC_TRANSACTIONS contains data for all currently circulated out titles. (Depending on your loan period and renewals, that could include titles that have been checked out quite a while).
- Use CIRC_TRANS_ARCHIVE to get past transactions for each title. (There is a separate entry for each checkout. Renewals are recorded in the same record).
Additional Information
- Circulation transactions are recorded in CIRC_TRANSACTIONS until the item is discharged, after which they are moved to CIRC_TRANS_ARCHIVE.
- Consequently, the discharge… fields in CIRC_TRANSACTIONS are always blank.
- When a transaction is archived, the value of circ_transaction_id is changed.
- In both tables, circ_transaction_id is assigned sequentially as a record is added.
- Nonetheless, you can join either table to REQUEST_HISTORY by circ_transaction_id.
- In CIRC_TRANS_ARCHIVE the Due_date is the due date at the time the item was initially charged out. If the item was renewed, the new date is not stored in this table.
- If you want to count renewals, use these two tables. If you need to know when or how renewals occurred, you'll need the RENEW_TRANS* tables.
- The patron_id field in CIRC_TRANS_ARCHIVE will always be 0 unless you have checked "Retain Patron ID" for Circ History in SysAdmin.
- For many circulation statistics, you will want to combine data from these two tables. Voyager provides an Access query called “Circulation Transactions (Charges)” which does this for you. From an Add Tables window in Access, click the Queries tab and you’ll find it.
- NOTE that CIRC_TRANS_ARCHIVE will retain transactions for withdrawals, however the ITEM_ID won't match on a title in the database unless you happen to retain the "delete.item" file in the /rpt directory (this file captures details on any deleted items).
- NOTE that the CIRCCHARGES_VW table has several constraints (for example, it does not include transaction on purged items) that may cause it to not provide the accuracy of data you seek. In that situation, for the most accurate query, please use the circ_transactions and circ_trans_archive tables.
- NOTE that although it may be counter-intuitive, the same CIRC_TRANSACTION_ID can appear in both CIRC_TRANSACTIONS and CIRC_TRANS_ARCHIVE because when a transaction is archived, the value of CIRC_TRANSACTION_ID is changed.
- NOTE that you can look at both tables at once by using "CIRCULATION TRANSACTIONS (Charges)". It is actually a subquery, but it works like a table. It is included with your Reports.MDB file. From an Add Tables window in Access, click the Queries tab and you’ll find it.
See also the Voyager Data Dictionaries and ER Diagrams.
- Article last edited: 03-May-2020