Invoicing Improvement Review

1. RACAR FALL MEETING 2009 Invoicing Improvement Review

2. 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

3. 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.

4. 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

5. 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

6. How do you link the ODBC Drivers to SCT Banner® • “File” • “Get External Data” • “Link Tables”

7. Locating the ODBC Database • Change “Files of type” to ODBC Database

8. Data Source Name • Select “Machine Data Source” tab • Select the appropriate “Data Source Name” • Click OK

9. Login Screens • Enter your username and password if prompted

10. SCT Banner® Tables • Select the table(s) containing the appropriate data • Click OK

11. Table Object List • The selected tables appear in Access under the “Table Object” list as a linked table.

12. 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)

13. Relationships • Setting Up Table & Query Relationships

14. Queries • Setting up the Query

15. 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

16. Using Main Switchboard

17. Query Sample

18. Extract Query Data to Word or Excel

19. 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

20. Invoice Worksheet

21. STUDENT INVOICE EXAMPLE

22. 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

23. THIRD PARTY CONTRACT INVOICE EXAMPLE

24. 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

25. 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

26. 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

27. Total Access Emailer

28. Total Access Emailer

29. Total Access Emailer

30. E-mail and Attachment Sent to Student

31. Questions ????