Home /OpenOffice.org 3 Course Package /Lesson One - Overview and Installation of OpenOffice.org 3 /Lesson Nine - Creating a Bulk Mailing List Using Base and Writer

Lesson Nine - Creating a Bulk Mailing List Using Base and Writer

Undefined
Lesson Objectives
In this lesson, you will learn the following:
(All the lessons here comply with the National Occupational Standards of E-Skills) 
  1. How to create and save a database using the new OpenOffice.org Base database application.
  1. How to add, edit and delete records within a database table.
  1. How to sort data within a table.
  1. How to register a database within OpenOffice.org for use by other applications within the suite.
  1. How to use Writer to create mailing labels from records within a Base database document.
Overview
In previous versions of OpenOffice.org, database files created with other applications could be accessed, edited and utilized for document creation within Writer and Calc. Moreover, a Calc spreadsheet document could be utilized as a spreadsheet table for use within Writer. However, prior to version two, OpenOffice.org did not have an application with a Graphic User Interface (GUI) similar to Microsoft Access or FileMaker Pro to easily create database files.
One of the major new features that can be found in version two of OpenOffice.org is a database application called Base. While the current version of Base may not have all of the features of a mature database application such as Microsoft Access or FileMaker Pro, Base has all of the fundamental features to create common business database documents, including the ability to create tables, forms, reports and queries. In this lesson, you will become acquainted with using Base by creating a bulk mail mailing list. Upon completion of this lesson, you will have learned the fundamentals of creating tables within a Base document, adding and deleting records within tables, how to sort records within a table and much more.
Getting Started
Before we do so, we need to open the Lesson Nine file that is available for use with this book. To open the file, follow these steps:
  1. If you have not already downloaded the lesson files for this course, click on the Lesson Files topic located in the course lessons area and select to download the .zip file to your computer's desktop.
  1. When the file appears on your computer’s desktop, double-click the file icon to unzip its contents and access the files associated with each lesson. Double-click the Lesson Files folder icon that contains the available lesson files, locate the Lesson 09 folder and double-click on it to access the file for this lesson. When you have opened the folder, double-click the file named lesson_09_start.odb to open the file.
  1. For this exercise, you need to save this file to your computer’s hard drive, a network storage drive or a readable/writable removable storage device, such as a floppy disk. To do so, click on the File menu and select Save As from the menu options that appear. Select the location where the database should be saved. In this example, use lesson_09_work as the filename for the saved document. Moreover, select the file type OpenDocument Database. Then click the SAVE button.
Creating a New Database
For this lesson, a file has already been created for you to begin working on. However, if you were creating your own database project, you would need to start with opening OpenOffice.org and creating a new Base document. To do so, follow these steps:
  1. Start the Base application from the Start Menu. Or within OpenOffice.org, go to the File menu, select New from the menu options that appear and select Database from the submenu options that appear.



  1. When the Database Wizard window appears, select the Create A New Database radio button and click the NEXT button located at the bottom of the window.
  1. In the next window, select to have the database registered by clicking on the YES radio button. Within the same window, click both checkboxes available to have the software open the database for editing and have the table wizard assist with creating the necessary tables for the new database. Then click the FINISH button located at the bottom of the window.

  1. When the Save As window appears, select the location where the database should be saved. In this example, use my_sample_database as the filename for the saved document. Moreover, select the file type OpenDocument Database. Then click the SAVE button.

  1. After the application has saved the database, the Table Wizard window will appear. In the Category selection area, select whether the database will be used for business or personal purposes by clicking on one of the radio buttons provided. Depending on the category selected, the Sample Table popup menu will provide a list of possible databases to create. For this example, choose the Business category and select MailingList from the Sample Tables popup menu.

  1. When a sample table has been selected, a list of available fields associated with the type of table will appear in lower half of the window. In the Available Fields selection area, click on a field to add to the table and click the SINGLE RIGHT ARROW button. The field will then appear in the Selected Fields selection area. Repeat the process for each field to be added to the table. For this example, select the following fields from the Available Fields selection area: Prefix, FirstName, LastName, Address, City, StateorProvince and PostalCode. When all appropriate fields have been selected to appear in the table, use the UP and DOWN arrow buttons to change the order in which the fields will appear within the table. Then click the NEXT button located at the bottom of the window.

  1. In the next window to appear in the Table Wizard, click on a field within the Selected Fields selection area. In the Field Information selection area, various formatting selections can be assigned to each field. Make the appropriate format options for the selected field. Then repeat the process for each field available in the Selected Fields selection area. For this example, all field information selections can remain at their default configurations. Then click the NEXT button.

  1. In the next window to appear, select the checkbox available to have Base create a primary key for the table and select the Automatically Add A Primary Key radio button. Then click the NEXT button.

  1. In the next window, type a name to identify the table being created using the text field provided. For this example, type the name MailingList if it doesn’t already appear within the text field. Moreover, select the Insert Data Immediately radio button. Then click the FINISH button to create the table for the database.

To remove the new table editing window from the screen, click on the File menu and select Close from the menu options that appear. Repeat the same process for the main window for the document my_sample_database. You may then delete the my_sample_database file from your computer and return to the lesson_09_work file.
Adding and Deleting a Field within a Table
When creating a new Base document, as performed previously, you had an opportunity to create a table and add fields to the table while completing the document creation process. However, users can add and delete fields after the table creation process has taken place. In the following exercise, you will create a new field to an existing table and then remove it from the table when completed. To add and delete a field within a database table, follow these steps:
  1. With the lesson_09_work file open, click on the Tables icon located on the left side of the document window within the Database Pane.
  1. In the Tables pane located at the bottom-right of the window, select the MailingList Table to be edited by single-clicking on the icon.

  1. Click on the Edit menu at the top of the application window and select Edit from the menu options that appear.
  1. When the Table Design window appears, click within the first available field underneath the Field Name column. Type a field name that best describes the information that will be entered into the field. When creating field names, be sure they do not contain any spaces. For example, a field name labeled Date Acquired should be typed as DateAcquired. In this example, type Organization for the new field name. When completing the Field Name, press the Tab key on the keyboard to proceed to format the Field Type.

  1. When tabbing to the Field Type column, a popup menu will appear to enable selection of the type of data the new field will contain. Select the appropriate field type, and make any additional configurations needed associated with the field within the Field Properties selection area located at the bottom of the Table Design window. In this example, the Field Type will remain Text [VARCHAR], which means variable character text, and the Field Properties will remain at their default configurations. Then press the Tab key on the keyboard to proceed to the Description column.

  1. In the Description column, type a description for the new field being created (optional).

  1. To save the changes made to the table, click the File menu and select Save from the menu options that appear. The process for adding the field Organization to the table has been completed. To double-check to make sure the field has been added to the table, minimize the Table Design window you are currently working in, return to the main document window, and double-click on the MailingList table. The table should contain the Organization field.

  1. To remove the organization field from the table, return to the Table Design window that you minimized in step #7. If you mistakenly closed the Table Design window, simply repeat steps #1 through #3 in this exercise to return to it. Once you have returned to the Table Design window, select the field by clicking in the grey cell located to the left of the field you wish to delete. In this example, click in the grey cell located just to the left of the field name Organization. When doing so, the entire row for the Organization field will be selected and a green arrow will appear within the grey cell to the immediate left of the field selected.

  1. Once the organization field has been selected within the Table Design window, click on the Edit menu and select Cut from the menu options that appear. The Organization field will disappear from the table. For this change to take place permanently, click on the File menu and select Save from the menu options that appear.

Adding, Editing and Deleting Records Within a Table
Once a table has been created, records can be entered into it. A record is simply data that is entered and saved within a table. Data entered within a record is associated with each other. For example, if you create a Base document to build a mailing list, the data you enter for a person’s name, address, city, state and zip code are associated with each other and, therefore, are entered within the same record.
In this exercise, we will open the table that has been created within the lesson_09_work file and add additional records to the fictitious mailing list. Once those records have been added, you will also learn how to edit and delete records within a table. To add, edit and delete records within a table, follow these steps:
  1. With the lesson_09_work file already open, click on the Tables icon located on the left side of the document window within the Database Pane.
  1. In the Tables pane located at the bottom-right of the window, open the MailingList Table to enter records by double-clicking on the icon.
  1. When the MailingList table window appears, you will notice there are already ten (10) records entered into the table. An empty record exists below the tenth to allow you to begin entering a new record. Within the empty record, place your mouse button on the ID field and click your left mouse button. This will allow you to select the ID field for entering text and numbers, much like selecting a cell within a Calc spreadsheet to enter data. When the empty ID field has been selected, type the number eleven (11) to uniquely identify the new record being entered. For each new record entered into the table, give it a unique identity by consecutively numbering each one. To advance to a new field within the record for entering data, simply press the TAB key on your keyboard like you would for advancing to the next column’s cell within a spreadsheet. Beginning with record eleven (11), enter the following addresses:

 

Ms. Latasha Hutchenson

14778 Sunset Ave.

Clinton

NC

28328

Mr. Raymond Bavaria

15654 Cayuga St.

Ithaca

NY

14850

Mr. Johnny Hillandale

P.O. Box 124457

Starkville

MS

39759

Ms. Maria Cozumel

204 Hwy. 24 W.

Kenansville

NC

28349

Mr. Thomas Shippman

24547 Pacifica Ave.

Berkeley

CA

94701

Ms. Kyle Montague

27440 Franklin St.

Chapel Hill

NC

27514

Mr. Adrian Macon

P.O. Box 61445

Cherokee

NC

28719

Ms. Elizabeth Yorkshire

P.O. Box 9115

Kingston, Ontario

CN

K7L 4W

Ms. Kelli Harrels

81157 Lumina Ave.

Wrightsville Beach

NC

28480

Mr. Marcos Jameson

81559 Rt. 12E

Cape Vincent

NY

13618

Mr. John Doe

123 Main St.

Anytown

NC

12345

 

  1. Once you have entered the additional records, the table should contain a total of twenty-one (21) records. To save the records entered, click the SAVE CURRENT RECORD button located directly beneath the File menu within the MailingList table window.

  1. Once a record has been added to a table, the data within it can be edited later if needed. Selecting data within a database record for editing is the same as selecting a cell within a spreadsheet. Simply click within the field that you wish to edit the data for. For example, place your mouse pointer on top of the City field for record number twenty-one (21) and click your left mouse button. The data “Anytown” is selected, and you can now begin typing “Charlotte” to change the name of the city. Remember to click the SAVE CURRENT RECORD button located directly beneath the File menu to permanently save the changes.

  1. To delete a record that has been entered into a table, right-click within the grey cell located to the left of the record you wish to delete and select Delete Rows from the contextual menu that appears. To delete record number twenty-one (21), right-click your mouse in the grey cell to the left of ID number twenty-one (21) and select Delete Rows from the contextual menu that appears. When the dialogue window appears to ask whether you want to delete the selected data, click the YES button. Then click the SAVE CURRENT RECORD button located directly beneath the File menu to permanently save the changes.

Sorting Data Within a Table
If this were an actual mailing list to be used to send mail, it would be a small enough list to simply print on one sheet of adhesive labels, apply the labels to envelopes and drop the envelopes in the mail. But if a mailing list database table contained five hundred (500) records, then it would take as much as twenty-five (25) sheets of standard-sized adhesive labels to print all of them. Moreover, sending a five hundred (500) person mailing might require doing so via bulk mailing, which in turn would require sorting the envelopes by zip code.
Rather than applying the labels onto envelopes and then sorting them by hand, the process could be simplified significantly by sorting the records via zip code within the Base application before printing the labels. Therefore, the mailing addresses would already be sorted in the correct order when printed onto the labels. The labels could be applied onto envelopes in the order in which they appear on the label sheets, eliminating the need to sort the envelopes afterwards.
To sort the records within the MailingList table by zip code, follow these steps:
  1. With the lesson_09_work file already open, click on the Tables icon located on the left side of the document window within the Database Pane.
  1. In the Tables pane located at the bottom-right of the window, open the MailingList Table to enter records by double-clicking on the icon.
  1. Using your left mouse button, single-click on the ZipCode field label located at the top of the MailingList table window. When doing so, the entire column of zip codes for each record will be selected.

  1. To sort the records by zip code, click the SORT ASCENDING button located within the Table Data View toolbar at the top of the window. When doing so, the records are sorted by zip code in numerical order and then by alphabetical order. When the labels are printed, the addresses will print in the order in which they appear on the screen.

At this time, the records within the table should remain sorted. However, if you ever wanted the records to reappear in their original order, you could do so by sorting the ID records in ascending order the same way you sorted the records by zip code.
Registering a Database Within OpenOffice.org
Before we can use Writer to create mailing labels from the database records, the database document we are working in must be registered within OpenOffice.org. If a database document is not registered within OpenOffice.org, then the Label Wizard will not be able to identify the correct database to extract the records from. If you had created the original database yourself using the Database Wizard, you could have specified to have the database automatically registered. Because you didn’t create the original file, however, you must perform the simple task of registering the database manually.
To manually register the database within OpenOffice.org, follow these steps:
  1. With the lesson_09_work file already open, click on the Tools menu and select Options from the menu options that appear.
  1. When the OpenOffice.org User Data window appears, click on the plus “+” icon located next to the OpenOffice.org Base option located on the left side. When doing so, a list of options will appear related to the Base application.

  1. Select Databases from the OpenOffice.org Base options that appear by clicking on the label. When doing so, the list of databases that are already registered within OpenOffice.org will appear in the Registered Databases selection area on the right side of the window. To register the lesson_09_work database document, click the NEW button, then click the BROWSE button in the Create Database Link dialogue window and locate the lesson file. After locating the file, select it and press the OPEN button. You will then be reverted back to Create Database Link dialogue window, where you can press the OK button to register the database.

  1. To complete the registration, click the OK button within the OpenOffice.org User Data window.
Using Writer to Create Mailing Labels from Database Records
Now that the address records have been entered into the database table and the document registered within OpenOffice.org, we are ready for creating the mailing labels. Unlike Microsoft Access, OpenOffice.org Base doesn’t have a Form Wizard for creating mailing labels. Instead, OpenOffice.org relies on a wizard within Writer to complete the process of creating mailing labels, which ultimately produces the same result. To create mailing labels from address records within a Base document, follow these steps:
  1. From any application within OpenOffice.org, including Base or Writer, click on the File menu, select New from the menu options that appear and select Labels from the submenu options that appear.

  1. A Labels window should now appear on the screen. At the top of the Labels window, there should be three tabs present: Labels, Format and Options. If it isn’t already selected, click on the Labels tab. Within the selection areas provided, select the following Label configurations:
  • Place a check within the Addresses checkbox
  • Select the lesson_09_work document within the Databases popup menu
  • Select MailingList within the Tables popup menu
  • Select the SHEET radio button
  • Select Avery Letter Size within the Brand popup menu
  • For this sample exercise, select 5261 Address within the Type popup menu. If this were an actual mailing list you were utilizing to prepare a mailing, you would select the type of labels you or your office have purchased.

  1. Continuing within the Labels tab window, select Name from the Database Field popup menu and press the LEFT ARROW button to place the field within the Inscription window. Then press the ENTER or RETURN key on your keyboard to move the cursor to the next line within the window.

  1. Next, select Address from the Database Field popup menu and press the LEFT ARROW button to place the field on the second line within the Inscription window. Then press the ENTER or RETURN key on your keyboard to move the cursor to the next line within the window.

  1. Select City from the Database Field popup menu and press the LEFT ARROW button to place the field on the third line within the Inscription window. Type a comma (,) immediately following the City field and press the SPACE BAR key once on the keyboard. Then select State from the Database Field popup menu and press the LEFT ARROW button to place the field behind the City field. Press the SPACE BAR key twice on the keyboard. Finally, select ZipCode from the Database Field popup menu and press the LEFT ARROW button to place the field behind the State field. Then press the ENTER or RETURN key on your keyboard to move the cursor to the next line within the window. The format displayed within the Inscription window indicates how the address information will be printed on the labels.

  1. Click on the Options tab at the top of the Labels window. When the selection areas appear within the Options tab, select the ENTIRE PAGE radio button and place a check within the Synchronize Contents checkbox. This will format the labels to print on an entire sheet of labels, as you would purchase from an office supply store. Moreover, the Synchronize Contents option means that the data will automatically sync with the data contained within the MailingList table. Finally, if you wish to setup the labels to print on a printer other than what is specified in the window, click the SETUP button and configure accordingly.

  1. Press the NEW DOCUMENT button within the Labels window. Writer will then create a template that will format a sheet of labels as specified in the previous selections.

  1. Within the Writer label template window, click the File menu and select Print from the menu options that appear. A dialogue window will appear stating that the document contains address data fields and asks whether you wish to print a form letter. Click the YES button.
  1. When the Mail Merge window appears, you should notice some of the addresses within the database document are viewable. Make sure the ALL radio button is selected to print all of the addresses within the database. Also, make sure the PRINTER radio button is selected.

  1. If you wish to print the addresses on actual mailing labels, make sure that your printer is loaded with the correct type of sheet labels, as selected in Step #2. In this case, the labels we chose for this exercise were Avery 5261 labels. Otherwise, click the OK button and the addresses will print on the regular paper presently loaded in the printer. The addresses should then print in the format presented in the Writer template. You will notice that when the addresses are printed, they are done so in ascending order by zip code, making it easier to prepare for bulk mailing.
Additional Resources
OpenOffice.org: Base Discussion Forum
http://www.oooforum.org/
General Information Regarding Databases via Wikipedia
http://en.wikipedia.org/wiki/Database
Review Questions
  1. For changes to tables and records to remain permanent within Base, what do you need to do prior to closing the document or application?
  1. What is a database record?
  1. What does a computer user have to do before they can utilize the data contained within a Base database document for use in a Writer document?
  1. (True or False) Unlike Microsoft Access, OpenOffice.org Base doesn’t have a Form Wizard for creating mailing labels.
  1. (True or False) Users cannot add and delete fields after the table creation process has taken place.