DATA BASE FUNCTIONS: USING EXCEL 03

BACK

CONTENTS:

1. If you are a first time PC user, go through the Intro to Win XP
2. If you are a first time Mac user, go through the Mac Intro
3. If you are a first time Data Base user review DBMS BASICS

Set up the data base with each field/category as a column and each record as a row, see example at the top. The name of your field is the top cell in the column.  Widen the column if needed to show your full field. In most cases press <Tab> to move to the next field and <Enter> to move down to the next record

A form rather then a a spreadsheet layout is often used for working with records in a data base.

• Select the cells containing the filed labels
• Choose Form from the Data menu:

• In the form dialog box that appears:

• Click New to add a new record at the end of the file. Note: Press <Tab> to move to the next field or <Shift><Tab> to move to the previous field
• Click Delete to remove a record
• Click Find Prev and Find Next  buttons to go through the records
• Click Criteria, place a specific information in the appropriate field name, then click Find Prev or Find Next to bring up records meeting my criteria.  In the example I am searching in the last name field for a specific student, "Oskamp"


• Click Close  to close the form dialog box

You can sort, numerically or alphabetically, on up to three categorys (fields) in the data base

• Click on any cell
• Choose Sort from the data Menu to obtain the sort dialog box:

• Click the down arrow by the Sort by box and choose the sort field (I choose Last Name)
• Repeat field selections if you want to simultaneously sort for more then one field by clicking the down arrow by the Then by  boxes and selecting the fields. NOTE The last Then by will determine the overall order in your data base.
• Click the Header row selection if it has not been selected or No header rowif a data cell is being displayed in the Sort by: box.
  
• Choose ascending (a, b, c,...) or descending (z, y, z,....) for each field specified
• Click OK and your data base will be sorted

Records that meet certain criteria, (overdue bills, immediate family members, birthdays, failing students, those who sent you a Christmas card), can be selected for special treatment (send a late payment notice, send a birthday card, etc.)

• Place the cursor in  the appropriate field
  
• In the Data menu choose Filter and drag across to AutoFilter  to obtain the filter dialog box:

• This will place a down arrow button by each field.  Click the arrow to see filter choices (I clicked the down arrow by midterm grade field in the sample data base,  _SampleGdeDB.xls.

• Select a filter choice. I choose the (Custom) and then made the following choices to select all students who were failing or close to failing the class.
  

• The results are the students with 62 or less are selected, in this case just one student.
  

• To show all your records choose Data/Filter/ Show All :

• To hide the filter arrows choose Data/Filter/AutoFilter

There are data base functions provided to perform calculations with numeric fields such as adding up how much a customer correctly owes.  Since we are using Excel for this example I suggest using spreadsheet formulas to do this.  Check  Spreadsheet Basics  to find how to do this.

Merging fields in a data base with a form letter is a frequent use of a data base.  All of us have received "personal" letters from Publishers Clearing House, charities, loan companies and for special offers on merchandise.  There are many uses for mail merge letters: a teacher could send a message to each student's parents with specific information for their child, a social worker could send a letter to each client with specific information for their case, a college instructor could create a letter with specific grades for each student. To do this with Excel and Microsoft Word:

Preperations

• Have your data base completed in Excel
• Possibly create your your form letter in Microsoft Word leaving a space for each field are create below.  Click here to download the sample form letter SampleMerge.doc for the midterm grade summary 
• Choose Letters and Mailings/Mail Merge Wizard from the Tools menu

• Word will display choices for your merge Letters, E-mail messages, Envelopes...:

____________________________________________________

Step 1 of 6: First click your selection.  We clicked Letters since we will merge an Excel DB with a form letter. Then click Starting documents:

____________________________________________________

Step 2 of 6: The next choices will appear at the bottom of the pane: Click Select recipiants

• The choices will appear at the top choose Use an existing list then Browse:

• In the Select Data Source window that opens, change the File of  type to .xls:

• Find your Excel DB in the Select Data Source window and the Select Table dialog will open. Click OK
  

• The Mail Merge Recipient dialog box will appear.  You can  delete any record by clicking the check box.  Click OK
  

____________________________________________________

Step 3 of 6: Click Next: Write your letter:

 

• Use your previously written letter are write your merge letter now.
• Now click on the appropriate location in your letter for a field from your data base then choose the item you will merge.  I cclicked More items:

• Insert Merge Field will open displaying the fields in my Excel DB
  

• Click the item to be inserted then click Insert.  The variable name will appear in your letter
• Click Cancel, the repeat by placeing your cursor in a new field location, click  More items then choose a new item to merge.
• Continue until all your field choices are inserted
  

____________________________________________________

Step 4 of 6: Click  Preview your letters  to view a letter

• Check your letter against the Excel DB to ensure you correctly merged your fields.

____________________________________________________

Step 5 of 6: Click  Complete the Merge

____________________________________________________

Step 6 of 6: You are given the choice to Print or Edit individual letters.  I usually choose Edit to create a file of letters that I can do one finel check before printing.

• I will be given choices for creating a new document.  I choose All
  

• A file with all of my merged documents will open.  I check it out, make any refinements and print the file.