1 / 46

Advanced MS-Access for Power Users

Advanced MS-Access for Power Users. By Indiana University of Pennsylvania Eric Parks. Presentation Goal. Make you more efficient at : Identifying and working with Banner data to create queries Writing MS Access queries. About IUP. 14,000 students; 1,800 employees Largest Member, SSHE

izzy
Download Presentation

Advanced MS-Access for Power Users

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks

  2. Presentation Goal • Make you more efficient at : • Identifying and working with Banner data to create queries • Writing MS Access queries

  3. About IUP • 14,000 students; 1,800 employees • Largest Member, SSHE • 3 campuses; 1 center; 1 academy • Doctoral I • Clock-hour programs

  4. Banner at IUP • Implemented five baseline modules and three “Web For” products 1998-2000 • Banner 5.x (soon to be Banner 6) • Oracle 9i, OAS (soon to be 9IAS) • Sun Solaris

  5. The Challenge Problem Statement • We were seeing an increase in the number of MS Access users not following proper practices for creating reports and pulling Banner data

  6. Topics • Getting Started • Other ways to get Reports/Data • Compacting • Poor Practices

  7. Getting Started 1. Defining your data 2. Understanding your data 3. Creating results 4. Presenting your results

  8. Defining Your Data

  9. Two Popular Methods Determine tables/views needed to create the query • Via Banner Client • Via Web site developed by IUP

  10. Banner Form Within Banner Form with desired data • Select the field with data you wish to use in your query and click Help >> Dynamic Help Query

  11. The help form shows the field name which will point you to the correct table. FTVACCT is the table the field FTVACCT_ACCT_CODE is stored in.

  12. Web Information Resource • Web-site created by IUP • Broken down by modules • Lists tables and views along with comments on the fields

  13. Understanding Your Data

  14. Primary Keys – Indexes • If queries and reports use the indexed fields, then the query runs much faster • If you use the indexed field in the underlying table associated with a view, the query will run faster even though views do not have listed indexes

  15. Query w/ Indexed field (SPBPERS_PIDM) Pidm is indexed so query zooms to correct record Super fast and efficient Query by non indexed field (SPBPERS_LEGAL_NAME ) Check 1st record for match – no match then check 2nd record for match etc until find match May go through MANY records to find match Very slow and resource consuming Example: SPBPERS

  16. Querying without an index • Would be like trying to find a particular file folder without any labels. You would have to go through each one until you found the one you were looking for. • However, if the folders were labeled (indexed) you could directly choose the correct one without having to look at all the other folders.

  17. Application Tables (Base/Repeating) • Base Table – ex SPBPERS • Contains ONE record for each key • SPBPERS_PIDM is the Key in this table so the output is one record per person • Repeating Table ex SPRADDR • Contains multiple records pointing back to a record in a base table

  18. Validation Tables • Used to verify values for a particular field; Values must be in the table in order to be allowed during data entry into an associated banner form • Characteristics of Validation tables • Have Code field and Description field • The _Code in a field name indicates there may be an associated Validation table • For reporting, the code field is often used to obtain the description from the validation table

  19. Validation TablesNaming Conventions • Example … STVATYP (address type) • S -> module student • T -> Table • V -> Table type of Validation • Last 4 characters refer to field name • SPRADDR_ATYP_CODE field has an associated validation table named STVATYP

  20. Views in depth • A view is a subset of one or more tables • Views can help optimize your applications • The user only needs access to the view, not the underlying tables

  21. VIEW Table 1: Field 1 Table 3: Field 1 Table 1: Field 2 Table 3: Field 2 Table 2: Field 1 Table 3: Field 3 Table 2: Field 4 Table 3: Field 4 Table 2: Field 6 Table 1 Field 1 Field 2 Field 3 Field 4 Table 2 Field 1 Field 2 Field 3 Field 4 Field 5 Field 6 Table 3 Field 1 Field 2 Field 3 Field 4 Field 5 Field 6 Field 7

  22. AS_Student_Data sgvstd1 Fields … sgvstd2 Fields … stvresdFields… Consists of fields from the following tables: Spridensprtelespraddrsorfolksgbstdnspbpersstvtermsprhold

  23. Querying SCT Views with required fields • Banner bookshelf or the hardcopy Reporting manual • Search for the view name

  24. Why Mandatory? • In order to obtain information at the level identified by the key attributes, you must supply these conditions when you create your query • The mandatory fields are the indexed fields/primary keys; When the indexed fields are used, the query is much more efficient • When the mandatory conditions are met, a subset of the data is returned which also speeds the query and reduces stress on the Banner system

  25. Example: AS_STUDENT_ENROLLMENT_SUMMARY Mandatory Conditions The following condition must be supplied for the view to report the information: • Term Code -- Term_Code_Key

  26. Student Listing Name State Phone Term The mandatory condition TERM_CODE_KEY was not supplied, so there is a row for every term in the database The mandatory condition TERM_CODE_KEY = ‘199501’ was supplied, so only the rows in that term were returned Name State Phone Term

  27. Tables vs. ViewsWhich one should you use? • Use tables for linear/simple queries • Use views for complicated queries involving multiple tables • Security Issues • A view may be used or created for sensitive data

  28. Creating the Results

  29. Expression Builder • Concatenation &or+can be used to join • NAME: [LAST_NAME]+", "+[FIRST_NAME]+" "+[MIDDLE_INITIAL] • Donlan, Michael J • TERM: [TERM_DESC]&" (“&[TERM_CODE_KEY]&")“ • Spring 2003 (200250) for term 200250 • IIf («expr», «truepart», «falsepart») • Local: IIf([STATE1]="PA","IN STATE","OUT OF STATE") • If state is PA then IN STATE would be displayed since it is true • Left$ («stringexpr», «n») • Year: Left$([TERM_CODE_KEY],4) • 2002 for term 200250 • Format («expr», «fmt») • Date: Format([ENROLLMENT_ADD_DATE],"mm/dd/yyyy") • Result example 10/10/2002

  30. LIKE Criteria for Last_NameLike “Jon*“ would return Jones, Jonet etc Not equal <> Criteria for field State1 <>"PA" IN Criteria for field Last_NameIn (“Smith”,”Jones") Between Criteria for Enrollment_Add_Date Between #4/2/2002# And #4/4/2002# Better way to get values between two dates is: >= #4/2/2002# and < #4/5/2002# AND OR Comparison Operators

  31. Presenting the Results

  32. Presenting MS Access Data Queries to another office product… Click on Tools >> Office Links from the menu • MS Access Reports • Word Merge • Export • HTML • MS Excel • Tab Delimited file • Report Snapshot Reports to another format… Click on File >> Export from the menu and then choose the type

  33. Other Reporting Vehicles • Job Submission (Banner) • Web Report

  34. Tables and views can change with each new release.How do we know if there are view or table changes?

  35. SCT Table & View Changes • The release guide will show you new and changed tables/views. You can do a search using the find & find again buttons if you want to know if a particular table/view has changed.

  36. Compacting the Database • Compact databases once a week • You can also set the db to compact on close Click Tools >> Options then choose the General tab.

  37. Query Analyzing The query analyzer can be used to show helpful information such as table indexes, column properties, relationships, parameters etc. Click Tools >> Analyze >> Documenter and choose your query. Clicking the options button allows you to specify what you want to see in the documenter

  38. Poor Practices • Using MS Access to store and/or update information that is used for business purposes • Queries that run for an extended period of time • Poor Organization of databases • Using Like or In on Key fields

  39. Using MS Access to store and/or update information that is used for business purposes • Banner should store all operational data and Access should be used to link to that data • Data stored in Access would not be updated when a Banner transaction occurs and would therefore become out of date quickly • If Access was used to store data, hard drive (or server) storage space would quickly become an issue

  40. Queries that run for an extended period of time • An extended period of time will vary based on the query; A query returning many records or containing many tables and views will take longer than a simple query • Closing your MS Access database does not stop the query from running behind the scenes

  41. Poor Organization of databases • Good naming conventions on databases allow users to easily identify • the database purpose • the query or report purpose • Copying an entire database to get a single query is not advised; You can import the query or report into your database from another Access database; This prevents multiple copies of the same/similar databases

  42. Using Like or In on Key fields • Using Like or In dramatically decreases query performance when used on a key field • Always specify the key field (such as Term) then if needed use like or in on a secondary field

  43. Questions Eric Parks eparks@iup.edu

More Related