Quick & Dirty Access Reports

1. Quick & Dirty Access Reports GIL User Group Meeting May 19, 2011 Cathy Jeffrey

2. Getting Started Download the Oracle 10g ODBC Drivers from the GIL Download site. �http://download.gil.gsu.edu/ User ID & Password are available from your Library Director or your Library Contact for the Service Site. Install the ODBC Drivers. Instructions are available on the Download site. Tip 1: Seek help with the installation. Ask your systems librarian or campus IT staff to assist you. If you need additional support contact the Service Site staff.

3. Access Report File Voyager loads with an access report file titled: reports.mdb Tip 2: You can rename this file so that it will not be replaced by the next Voyager upgrade.

4. Voyager Access Reports Voyager uses Access Tables, Queries and Reports • Tables are created by Voyager. They should not be changed. • Voyager also comes with pre-packaged queries and reports • Using, creating or editing queries and reports is facilitated by an understanding of how information is stored in Voyager: bib records, holding records, item records, purchase orders, invoices, patron records

5. Prepackaged Queries & Reports

6. Creating Queries There are several ways to create a query in Access • With the help of the Query Wizard • From scratch in design view • By entering new or copied SQL coding • By copying and editing an existing query Tip 3: When creating a new query use a prefix as part of the query name to help identify all of your own queries.

7. CreatingQueries Using the Query Wizard

8. Creating Queries Query Design

9. Creating queries Create a simple query in design view. Tip 4: Queries that appear to be well constructed can result in incomplete or inaccurate data.

10. Creating queries Titles by location query. Tip 5: Use linking tables like the BIB_MFHD Table or the MFHD_ITEM Table to link tables that do not share key fields.

11. Creating queries Tip 6: Queries use I D numbers for many components rather than the codes and names which are familiar to operators. These ID numbers can be found in the tables.

12. Creating Queries SQL View of the Titles by location query. Tip 7: When creating queries by copying SQL from another library remember that there may be elements in the query that will have to be changed for your database.

13. Creatingqueries Creating queries by copying and editing an existing query. Tip 8: NEVER edit a prepackaged query. Always “save as” before editing

14. Sample query Shelflist query. Tip 9: When sorting call numbers, always sort by the Normalized call number but use the Display Call Number in any reports.

15. SampleQuery New Materials LIst Tip 10: Use correct syntax in the query criteria. Look for the correct forms in existing queries and copy into new queries.

16. Sample Query Invoice list by ledger.

17. Sample Query Count of Circulation Transactions by LC Classification

18. Creating Reports • Use the Report Wizard to Create Reports: • Select the queries or tables that contain information needed in your report • Select the elements that you would like to display in your report • Select the style that you would like to use in your report

19. Access Report Tips • An Access report is “prettier” than Access query results. • An access report will typically require more pages to print than the corresponding query - sometimes twice as much = There is a price to being “pretty”! • Access reports can be edited in design view but be cautious. Only headers and design details can be edited. The text identifying data elements cannot be edited. Doing so will “break” the link to the associated query or table.

20. Sample Report

21. Questions? Cathy Jeffrey Clayton State University LibraryCathyJeffrey@clayton.edu 678-466-4336