Mail Merge with Banner Data

1
Mail Merge with Banner Data

• Distance Learning
• Chapman 633/632
• mxccedtech_at_gmail.com
• www.mxcc.commnet.edu/distance
• [phone]/5783
• Workshop Web Site
• www.mxcc.commnet.edu/workshops

2
Steps

• Import the Banner Extract to MS Access
• Run a query with selected fields
• Run and print a label report
• Create the letter in MS Word
• Mail merge with the letter.

3
Run a Banner Extract

• Demonstration
• Example
• SWRXF02-Registered Seats.
• Log in Banner.
• Go to www.banner.commnet.edu.
• Click Production. Wait for a few seconds for
  loading JAVA Applets.
• Enter username and password. Click Connect.
• Type the module name SWRXF02 in Go box and hit
  Enter.

4
Run a Banner Extract -Term Code Structure

• Click the blank box under Printer to display the
  parameters.
• Click the blank box of the 01 Term row.
• Type the Term Codes for the Fall 06, 106306.

5
Term Code Structure

• The term code for the Fall 2007 at MxCC should be
  107306.
• The term code for the Spring 2008 at MxCC should
  be 108106.

6
Search for Values

• Click the blank box of the 02 row, Seat Level.
• Click the down arrow (Search) next to Value
  .
• The value code validation window displays. If
  you would like to run the extract of all MxCC
  credit course. Double-click F1. F1 is then
  entered to the value box for row 02.

7
SWRXF02 Extract Codes, Submit, Save

• For the row 3, you may click the Search icon and
  see the list of codes. If you need the code for
  a full term, double-click 1. For summer online
  classes, leave this field blank.
• Row 04, click the Value box. If you need the
  header, keep the default value of Y.
• Row 05, click the Value box. If you need to
  exclude Tech-Prep students, keep the default
  value, E.
• For row04, 05, you may use the Search Icon to
  find out what the code represents.
• Submit
• Click the Radio button of Submit (bottom right).
• Save
• Click Save icon (upper left corner).

8
Run the Extract

• After clicking the Save icon, the Banner is
  running the Extract.
• Wait for 30 seconds to minutes, depending on the
  size of the extract.
• Once the job is completed, you see the extract in
  .pdf format. This .pdf file cannot be used to
  import to a MS application such as MS Access or
  MS Excel.
• Minimize the .pdf file window by clicking the -
  at the upper right corner of the window.

9
Run the Extract-Text File

• Go to Options menu and select Review Output
  (GJIREVO).
• Click the blank box of the first row under File
  Name in the table. A list of files displays.
• Double-click the second file with .lis extension
  and your Internet browser will open the file.
  This is the extract file you can save to your
  computer.

10
Save the Extract to Your Computer

• Go to File menu and select Save As...
• In Save as type pull down menu, select Text File.
  
• You need to rename the file in order to import it
  to MS Access correctly.
• Highlight the file name f_yguan_swrxf02_63659161
  _lis, type a new name, such as
  registration101005.
• Click My Document.
• Click the New Folder icon and type the folder
  name as mailmerge.
• Click Save.
• The saved .txt file can be used to import to a MS
  Application such as MS Access or MS Excel.

11
Step1, Import the Banner Extract to MS Access

• Open MS Access and create a blank database named
  studentdata.mdb.
• StartgtProgramsgtMS OfficegtMS Office Access 2003
• Click Create a new file at the bottom right of
  the Task Pane.
• Select Blank Database at the upper right of the
  Task Pane.
• Click My Documents and click Mailmerge folder.
• Highlight the file name db1.mdb and type the
  database name such as studentdata.
• Click Create.

12
Import the Banner Extract to MS Access

• Go to File, select Get External Data and Import.
• In the Import window, select the Files of type
  pull down menu. Select Text Files.
• Double-click the extract file you downloaded from
  Banner previously.

13
Import Banner Extract to MS Access

• Click Next in the first screen of the Import Text
  Wizard.
• In the second screen of the Import Text Wizard,
• Check First Row Contains Field Names. This is
  to ensure that the column headings in Banner are
  imported to the first row of an MS Access table.
• Pull down the Text Qualifier menu and select .
  This is to ensure the columns in the Banner
  extract are correctly imported to an MS Access
  table.
• Click Next.

14
Import Banner Extract to MS Access

• In the third screen, click Next.
• In the fourth screen, click Next.
• In the fifth screen, check No Primary Key and
  click Next. When No Primary Key is checked,
  Access will ask you to set up a primary key
  yourself.
• In the sixth screen, the default table name is
  the same as the text file name. Click Finish if
  you dont need to change the table name.
• The Banner extract file is then imported to a new
  table MS Access.

15
Step 2, Run a Query with Selected Fields

• Example
• Students registered for online classes in August.
• ID, First Name, Last Name, CRN, Course Name,
  Address, Registration Dates, Class Type.
• Select Queries object icon.
• Double-click Create Query in Design View.
• Select Table registration101005.
• Click Add button. Click Close.

16
Run a Query with Selected Fields

• In the table field window, double-click on the
  following fields you need in the query.
• ID, Last_Name, First_Name, CRN, Subj, Crse_Nr,
  Sect, Title, Schd, Sts_Date, Addr_line1, City,
  State, Zip.
• Click the gt at the bottom of the window to show
  the fields Schd and Sts_Date.
• In Criteria row
• Under Schd column, type D and hit enter. This
  enters the Distance Learning class type in the
  class type field.
• Under Sts_date, type Between 8/01/05 and 8/31/05
  and hit enter. This enters August for
  registration time frame.
• Click Datasheet View icon to see the student
  information.
• To save the query, click Save icon, type a name,
  and click OK.

17
Delete Duplicate Records in a Query

• The query previously ran has duplicate records
  because some students register 2 or more courses.
• To delete the duplicated records, you need to go
  back to Table view.
• Click Tables icon in Objects.
• Rename the table as registration101005_duplicates
  .
• Select the table and click the table name. Type
  the new name.
• Copy the table structure to a new table named
  registration101005.
• Select the table registration101005_duplicates.
  
• Click the copy icon and click the paste
  icon
• Enter the new table name registration101005.
• Check Structure Only.
• Click OK.
• Set up the ID as primary key in the new table.
• Select the new table and click Design view icon
• Click the primary key icon and click the
  ID field to set up the Banner ID as the primary
  key.
• Close the table by clicking the close icon at the
  upper right corner of the window. Click Yes to
  conform.

18
Append the Duplicated Table to Non-Duplicated
Table

• Select the duplicated table registration101005_du
  plicated.
• Click the copy icon .
• Click the paste icon .
• In the Paste Table As window,
• Type the new table name Registration101005.
• Check Append Data to Existing Table.
• Click OK.
• Click Yes to confirm deleting duplicated records
  in the new table.
• The data from the duplicated table are pasted to
  the new table with non-duplicated data. Because
  the ID field is setup as primary key, a field
  with only unique values (non-duplicated).
• Since the non-duplicated table is named as the
  same name from which the query is generated. The
  query will automatically pull out the
  non-duplicated data.

19
Step 3, Run a Label Report

• Click Reports icon from the
  Objects.
• Click the new report icon
• In the New Report window, select Label Wizard.
• Select the query online registration.
• Click OK to run the label report wizard.

20
Select Label Type

• Make selections for the type of labels you will
  be using. The type of labels is indicated on the
  label box.
• Click Next.

21
Select Fonts

• You may select a font, size, and etc.
• Click Next.

22
Enter Label Fields

• Enter the name and address fields into the label.
• Select First_Name, click gt to enter the field to
  the label. Hit space bar once.
• Select Last_Name, click gt
• Select Addr_Line1, click gt
• Select State, click gt
• Select City, click gt
• Select Zip, click gt
• Click Next button.

23
Sorting Field in Labels

• You may select a field to sort the labels.
• Select Last_Name, click gt
• Click Next button.

24
Name of the Report

• You may keep the default name for the label
  report.
• Click Finish.
• Click OK when prompted that some data may not be
  shown in the report.

25
Print the Label Report

• You may feed a regular paper to test out the
  printing of the label.
• Feed a regular paper in the printer.
• Go to File, select Print
• Check Page, type 1 and 1 to set up to print
  only one page.
• Click OK to confirm.
• Print the label
• Once you are ready to print the labels, feed the
  labels in the printer.
• Go to File, select Print
• Check All to print all pages.

26
Mail Merge the Address to A Letter

• Write the letter in MS Word.
• Run the Mail Merge Wizard.
• Go to Tools
• Select Letters and Mailings
• Select Mail Merge

27
Mail Merge Wizard Step1, Step 2

• Step 1, keep Letters checked and click Next
  Starting document.
• Step 2, Keep Use the current document checked
  and click Next Select recipients.

28
Mail Merge Wizard, Step 3

• Click Browse
• Click My Documents, locate the folder where you
  save the database file.
• Double-click the database file to open it.

29
Mail Merge Wizard, Step3, Step 4

• Select the query (online classes) in the
  database.
• Click OK.
• The next screen shows the query with student
  names and addresses.
• Click OK.
• Step 4, click Next Write your letter.

30
Mail Merge Wizard, Step 5

• Click Next Write your Letter in the step 4.
• Click More Items.
• Click First_Name field, click Insert.
• Click Last_Name field, click Insert.
• Click Addr_line1 field, click Insert.
• Click City field, click Insert.
• Click State field, click Insert.
• Click Zip field, click Insert
• Click Close button to close the Insert Merge
  Field window.

31
Format the Merged Fields

• Insert a space bar between First_Name field and
  Last_Name field.
• Insert paragraph break (Enter key) to move the
  Addr_line1 and City/State/Zip fields to different
  lines.
• Select the First_Name field and click the copy
  icon.
• Click after Dear and click paste icon to paste
  the First_Name field after Dear.
• Click Next Preview your letter.

32
Mail Merge Wizard, Step 5

• Use gtgt or ltlt to navigate to individual letters
  with different names and addresses.
• Click Next Complete the merge.

33
Mail Merge Wizard, Step 6

• Select Print
• Select Current record. Click OK. This is to
  print a test letter with the current individuals
  names and address.
• Should you need to revise the letter layout,
  click Previous Preview your letter.
• Revise the letter.
• Click Next Complete Merge.
• When you are ready for mass printing, click
  Print, keep All selected, and click OK.

34
Unmatching Query in Access

• Exercise
• Query1 is run one week prior to a semester
• Query2 is run two weeks prior to a semester.
• Run a query to sort out the newly registered
  students to whom send letters.
• You need to run an unmatching query to compare
  the two queries and take out the newly registered
  students.

35
Run an Unmatching Query

• Click Query in Objects column.
• Pull down the arrow next to New Object icon and
  select Query.
• In the New Query window, select Find Unmatched
  Query Wizard and click OK.

36
Run an Unmatching Query

• Select the first table/query and click next.
• Select the second table/query and click next.
• Select the matching field in each table/query and
  click Next.
• Select the fields in the result query and click
  Next.
• Click Finish.
• The result query only displays the unmatching
  records between the two tables/queries.