advanced ms-access for power users by indiana university of pennsylvania eric parks
TRANSCRIPT
![Page 1: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/1.jpg)
Advanced MS-Access for Power Users
By
Indiana University of Pennsylvania
Eric Parks
![Page 2: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/2.jpg)
Presentation Goal
Make you more efficient at :
Identifying and working with Banner data to create queries
Writing MS Access queries
![Page 3: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/3.jpg)
About IUP 14,000 students; 1,800 employees Largest Member, SSHE 3 campuses; 1 center; 1 academy Doctoral I Clock-hour programs
![Page 4: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/4.jpg)
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
![Page 5: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/5.jpg)
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
![Page 6: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/6.jpg)
Topics
Getting Started Other ways to get Reports/Data Compacting Poor Practices
![Page 7: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/7.jpg)
Getting Started
1. Defining your dataDefining your data
2. Understanding your dataUnderstanding your data
4. Presenting your resultsPresenting your results
3. Creating resultsCreating results
![Page 8: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/8.jpg)
![Page 9: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/9.jpg)
Two Popular Methods
Determine tables/views needed to create the query
Via Banner Client Via Web site developed by IUP
![Page 10: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/10.jpg)
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
![Page 11: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/11.jpg)
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.
![Page 12: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/12.jpg)
Web Information Resource
Web-site created by IUP Broken down by modules Lists tables and views along with comments
on the fields
![Page 13: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/13.jpg)
![Page 14: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/14.jpg)
![Page 15: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/15.jpg)
![Page 16: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/16.jpg)
![Page 17: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/17.jpg)
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
![Page 18: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/18.jpg)
Example: SPBPERS
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
![Page 19: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/19.jpg)
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.
Querying without an index
![Page 20: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/20.jpg)
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
![Page 21: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/21.jpg)
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
![Page 22: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/22.jpg)
Validation Tables Naming 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
![Page 23: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/23.jpg)
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
![Page 24: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/24.jpg)
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
VIEWTable 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
![Page 25: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/25.jpg)
sgvstd1
Fields …
AS_Student_Data
sgvstd2
Fields …
Consists of fields from the following tables:
Spridensprtelespraddrsorfolksgbstdn spbpersstvterm sprhold
stvresd
Fields…
![Page 26: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/26.jpg)
Querying SCT Views with required fields
Banner bookshelf or the hardcopy Reporting manual
Search for the view name
![Page 27: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/27.jpg)
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
![Page 28: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/28.jpg)
Example: AS_STUDENT_ENROLLMENT_SUMMARY
Mandatory ConditionsThe following condition must be supplied for the view to report the
information:
Term Code -- Term_Code_Key
![Page 29: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/29.jpg)
Student Listing
Tim Parker PA 243-9765 199301
Tim Parker PA 243-9765 199403
Tim Parker PA 243-9765 199501
Allison Sharp OR 624-8339 199501
Anne Turner IL 978-4122 199403
Anne Turner IL 978-4122 199501
Name State Phone Term
Tim Parker PA 243-9765 199501
Allison Sharp OR 624-8339 199501
Anne Turner IL 978-4122 199501
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
![Page 30: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/30.jpg)
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
![Page 31: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/31.jpg)
![Page 32: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/32.jpg)
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
![Page 33: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/33.jpg)
Comparison Operators
LIKE Criteria for Last_Name
Like “Jon*“ would return Jones, Jonet etc Not equal <>
Criteria for field State1<>"PA"
IN Criteria for field Last_Name
In (“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
![Page 34: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/34.jpg)
![Page 35: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/35.jpg)
Presenting MS Access Data
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
Queries to another office product… Click on Tools >> Office Links from the menu
![Page 36: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/36.jpg)
Other Reporting Vehicles
Job Submission (Banner) Web Report
![Page 37: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/37.jpg)
Tables and views can change with each new release.
How do we know if there are view or table changes?
![Page 38: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/38.jpg)
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.
![Page 39: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/39.jpg)
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.
![Page 40: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/40.jpg)
Query AnalyzingThe 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
![Page 41: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/41.jpg)
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
![Page 42: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/42.jpg)
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
![Page 43: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/43.jpg)
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
![Page 44: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/44.jpg)
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
![Page 45: Advanced MS-Access for Power Users By Indiana University of Pennsylvania Eric Parks](https://reader036.vdocuments.us/reader036/viewer/2022062417/5514fea1550346b0338b653e/html5/thumbnails/45.jpg)
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