Transcript No Slide Title

RACAR
FALL MEETING 2009
1
Topics of Discussion
System Requirements
 Linking to SCT Banner® Tables using ODBC
data sources
 Creating queries and tables
 Exporting data
 Student Invoices
 Third Party Contract Invoices
 Emailing Invoices

2
System Requirements
Access and Excel should be
installed locally on each user’s
computer.
 Access uses ODBC (Open
Database Connectivity) drivers to
connect to SCT Banner® Tables.
 Users install ODBC drivers locally
via a network application.

3
Why MS Access for Banner
Reporting/Invoices?
Banner reports lack data you need
 Desire greater flexibility to sort
 Export data to Excel
 Merge data to Word or e-mail
 Use Banner Views created by SGHE

4
Important Information

MS Access cannot update Banner data
 Banner data is read-only

IT department may need to grant
additional Banner privileges – ODBC
roles
 Limit what data you can see
5
How do you link the ODBC Drivers to SCT
Banner®
“File”
 “Get External Data”
 “Link Tables”

6
Locating the ODBC Database

Change “Files of
type” to ODBC
Database
7
Data Source Name
 Select
“Machine
Data Source” tab
 Select the
appropriate “Data
Source Name”
 Click OK
8
Login Screens

Enter your username
and password if
prompted
9
SCT
®
Banner
Tables
• Select the table(s)
containing the
appropriate data
• Click OK
10
Table Object List
 The
selected
tables appear in
Access under
the “Table
Object” list as a
linked table.
11
Main Accounting Tables
TBRACCD – Accounting Data (TSAAREV)
 SPRIDEN – Person Information (SPAIDEN)
 SPRADDR – Address Information
(SPAIDEN)
 GOREMAL – E-mail


Tables are linked by “PIDM” record
(Relationships)
12
Relationships

Setting Up Table & Query Relationships
13
Queries

Setting up the Query
14
Using Main Switchboard
Organized Form of Previously
Developed Queries and Reports
 Point & Click to retrieve data
 Query Parameters to limit population to
what you need
 Additional Queries and Reports can be
added

15
Using Main Switchboard
16
Query Sample
17
Extract Query Data to Word or Excel
18
Creating Student Invoices
All invoices use the Bill Date to determine
what invoice type should be created (1st
notice, 2nd notice, etc.)
 Initial query is executed and data is
compiled in a “Make-Table” – Invoice
Worksheet

 Data can be changed – not linked to Banner
When initial query is executed – pop-up
prompts will be displayed requesting the
bill dates.
 Once data is verified – invoices can be
created

19
Invoice Worksheet
20
STUDENT
INVOICE
EXAMPLE
21
Creating Third Party Contract
Invoices

Invoices are created based on three
tables:
 AS_THIRD_PARTY_CONTRACTS
 AS_STUDENT_ATTRIBUTE
 TBRACCD

All data is combined into one invoice for
each student
22
THIRD PARTY
CONTRACT
INVOICE
EXAMPLE
23
Total Access Emailer
Designed specifically for MS Access
 Runs as an add-in and includes an
interactive, Wizard-like user interface,
making it easy to customize your email
blasts
 Ability to send personalized emails and
attach files

24
Total Access Emailer

Unlimited Custom Emails
 Send Email to Everyone
 Personalize Email
 Attach Files to Email (PDF format)
 Send Your Email Again
 Send to People You Have not Emailed
 Send HTML and/or Text Messages
 Show Custom FROM Address
 Schedule Email Blasts
25
Total Access Emailer




Approved by State Accounting Office
Optional Audit Log Feature – keeps track of who
received an email along with the date and time.
Saves money on labor, postage, paper, and
envelopes
http://www.fmsinc.com/MicrosoftAccess/Emails.asp
26
Total Access Emailer
27
Total Access Emailer
28
Total Access Emailer
29
E-mail and
Attachment Sent
to Student
30
Questions ????
31