Database/MailMerge Assignment

CS110 - Introduction to Computers and Applications
SPRING 2008 - Database Management / MailMerge Assignment

Due Date:	April 10 in recitation - do not submit this assignment to Sakai
Evaluation:	15 points

* do not submit to Sakai *	* do not submit this assignment to Sakai *

Database Printouts:	Data Entry Form Picture, saved as .png file
	Datafile with twelve records in List mode, including a Footer
	Found subset of the datafile in Browse mode
	Found subset of the datafile in List mode
	Sorted datafile in Report mode

MailMerge Printouts:	Word processing form letter, with merge markers included, saved as .png file
	Three form letters, with your name and TA name on each letter
	staple all printouts together, attached with cover sheet do not use a paperclip do not submit to Handin

Cover Sheet Printout:	Cover sheet with name, section, TA name, assignment name, assignment due date

Related Materials:	Text: Rubin, chapters 7, 14 (online, Alexander Library Reserve) If the Rubin link gives a Session Time Out message, click here to go to Sakai and under Announcements look for directions to the online reserve materials or click on the Library eReserves in the left panel of the course Sakai site. Do not print the Rubin pages -- refer to them as needed to complete this assignment.
	Database Assignment Self-Check: click here
	Database Features Reference Sheet

Preparation

It is important that you read about and understand the Database application of AppleWorks before you begin this assignment. Some commands of the AppleWorks database tool are less intuitive than features in word processing, spreadsheet, and graphics, and there are a number of key operations to concentrate on as you create and use the database tool.

Read Chapters 7 and 14 in the Rubin text (online reserve, Alexander Library)
Remember to save your work often (about every five minutes); and know how to retrieve the most recent version of the file saved. Do not save junk files -- if you are dissatisfied with the document currently in RAM, do not save this file. Instead, close the document currently in RAM and reload the older version of the file.
Review the Database Features Reference Sheet. While you do not have to demonstrate knowledge of all the features listed on the page, use it as a guide for basic worksheet construction, editing, and formatting information and then to expand your knowledge of advanced features of Excel.

The AppleWorks Database Assignment

For this assignment, use the AppleWorks Database tool to:

design and print a data entry form
create a twelve-record datafile named music using the five records listed below and seven additional records you enter by searching the web for information (or use fictitious data)
print the complete datafile (twelve records) in List Mode, a sorted version of the datafile in Report mode, and selected subsets of the datafile in Browse and List modes
create form letters using the music datafile and Mail Merge feature of AppleWorks Word Processing tool

As you work with the AppleWorks database tool, remember that each of the operating modes -- Browse, List, Find, Layout -- offers different options and may have different menus. You may find it helpful to first review the Database Features Reference Page and create a sample file to practice the required AppleWorks features and options before you begin this assignment.

Note: This assignment is not submitted to Sakai. You will give the completed database assignment to your TA in recitation.

Creating a Data Entry Form and Datafile (Browse Mode, List Mode, Layout Mode):
Imagine you are developing a database file that summarizes your favorite music artist/group, your favorite CD by each artist, and information on an upcoming concert for each artist/group. The datafile will include names of the artists, favorite CD, location and URL of an upcoming concert, and other pieces of helpful information.

1. Name and define ten fields: Record Number, Artist, Favorite CD, Concert Date, Location, URL, Ticket Price, Number of Tickets, Processing Fee, Payment Type
a. classify Record Number, Ticket Price, Number of Tickets, and Processing Fee as Numeric fields; Concert Date as a Date field; Payment Type as a Radio Button field with options set at Debit, Credit, Check; and all other fields as Text.
b. add a new field titled Total Cost and define it as a Calculation field. Enter a formula that calculates the total cost of tickets, equal to Ticket Price times Number of Tickets plus Processing Fee (one processing fee for any number of tickets). The datafile now has eleven fields.
Note: Use parentheses to control order of operations in the calculation field.
Hint: To develop the Calculation field, click on the fields used in creating the formula, and only use the keyboard to enter needed data.

2. Using Layout mode, design a Data Entry Form that includes all of the above fields and set a Tab Order layout to match the sequence you will follow as you enter data.

3. Before you begin entering data, set the print orientation to Landscape and Print a picture of the data entry form you created (Browse Mode Layout will become the active window when you click Done after creating the form). To take a picture of the active window, simultaneously press the Apple-Shift-3 keys (listen for the click) and OS-X will create a .png file. Save the .png file to the desktop or to my.Rutgers briefcase file.
Print the .png picture file (Printout #1).

4. Use the data below and data you collect from the world wide web (or fictitious data) to build the music data file. Remember you are gathering data for a number of different artists. As you enter data, check the Tab Order options you have set and use the Tab Key to move from field to field.
Note: You should be in Browse or List mode when entering data or you may lose the data.

As you enter data to fill the twelve records, use these guidelines:

    •   enter names of individual artists or groups
         Note: Where group/artist are no longer together (i..e, Beatles), it is understood the concert is not a live concert
    • enter dates in the Concert Date field between April 12 and May 8, with some dates repeated
   • enter Debit, Credit, or Check in the Payment Type field
   • enter a variety of values in the Ticket Price field, with some values repeated
    •   if there is no processing fee for a particular concert, enter zero in the field

Music Datafile:

Record
Artist
Favorite CD
Concert Date
Location
URL
Ticket Price
Number of Tickets
Processing Fee
Payment Type
Total Cost

1

Usher

Confessions
April 26

Philadelphia

digphilly.com

82
4
12.00
Debit

2

Fleetwood Mac

Rumours
May 4

Los Angeles

onlineseats.com

65
2

0

Debit

3

Sheryl Crow

Wildflower
April 18

Denver

ticketsnow.com

110
6

14.65

Credit

4

Blind Boys of Alabama

Higher Ground
May 4

New Orleans

ticketsnow.com

75
10

0

Debit

5

Beatles

Sergeant Pepper
April 29

Boston

concerttickets.org

80
3

27

Check

5. Add seven additional records to the datafile for a total of twelve records.
6. Format the Ticket Price, Processing Fee, and Total Cost fields as Currency.
7. Format the Date field to be Month, Day, Year.
Hint: To set formatting options, doubleclick on the column title
8. Add a Footer with your name, section, and assignment due date.
9. Print the completed datafile in List mode (printout #2)

Using Search, Find, and Browse Modes:
10. Using the following search criteria, Print three subsets of the datafile (Printouts #3, #4, #5)

1. Browse Mode:

Ticket Price less than $65 with no processing fee

2. List Mode:
Concerts after April 26 that are not in New York or concerts with more than four tickets purchased and paid by Debit with a total cost no higher than $200

3. List Mode:
Concerts in Boston no later than April 20 with at least six tickets purchased or no more than four tickets purchased by Credit with no processing fee

Note: If the computer returns the message No Records Matched for either of your searches, Print a Picture of the Find window in .png format as it appeared when you entered the search criteria and submit this printout with your assignment.

Editing the Datafile:
11. Modify the file structure:
a. Delete the Favorite CD field
b. Change field names:
Ticket Price to Price
Payment Type to Payment

Sorting the Datafile and Printing a Report:
12. Sort the datafile in descending order with Total Cost as the primary field and Price as the secondary (tie-breaker) field.
13. Print a Report of the sorted datafile which includes the following fields, in this order: Record, Artist, Concert Date, Location, Number of Tickets, Total Cost (Printout #6)

Using Mail Merge to Create Form Letters

After searching on the web for information on the concerts you would like to attend, you decide to send letters to the artists to ask if there will be a meet the artist discussion session after the concert. Use the AppleWorks Mail Merge feature to send a personalized, original letter to contact the first three records listed in the datafile, creating three individual documents.

14. Open the music datafile and select Browse or List mode. Without closing the music datafile, open a word processing file named letter. This new file (letter) will include the text you send to the first three artists listed in the musicdata file, with each being an individualized letter.
a. begin your letter with the salutation Dear followed by a merge marker for the Artist field followed by a colon (:)
b. continue writing the form letter, using the Mail Merge command to insert merge markers wherever data from the file will be incorporated into the text of the letter. Your letter should give the date and location of the concert you will attend, mention how many will be in your group, and include a comment on the importance of the webpage in locating and purchasing tickets. The last paragraph of the letter should say you are looking forward to the concert and you hope you will have the opportunity to meet the artist in person following the concert. Your letter should include at least five merge markers.
c. when you have finished writing the letter, insert two blank lines and type your name, section number, and TA name at the left side of the letter.
d. Save the file to my.Rutgers briefcase.
e. Print a copy of the letter with merge markers included -- simultaneously press the Shift-Apple-3 keys and OS-X will create a .png file. Print the .png file (Printout #7).

15. Click the Merge button in the MailMerge window and select the Save in a New Document option in the next window.
All twelve letters will then be visible in a new document. Print only the first three of the twelve letters from the new document created by the Merge command. (Printout #8, #9 #10).

16. Staple the three form letters, merge marker letter, and datafile printout together with the database printouts before giving them to your TA.

Prepare a Cover Sheet
If you did not save the cover sheet you prepared as the first assignment for Sakai, open a word processing program and create a cover sheet to attach to the seven printouts. The cover sheet should include your name, section, TA name, assignment name, and assignment due date. Your assignment will be considered incomplete without the cover sheet.

Reminders:

Copy and Print the Database Checklist and review your printouts before submitting them to your TA.
Attach a cover sheet which includes your name, section number, TA name, assignment due date, and assignment name.
Staple all papers together -- do not use a paper clip or fold over the corners. One point will be deducted from your score if all papers are not stapled together.
Do Not submit this assignment to Sakai.

Return to:

CS110 HomePag e

Record	Artist	Favorite CD	Concert Date	Location	URL	Ticket Price	Number of Tickets	Processing Fee	Payment Type	Total Cost
1	Usher	Confessions	April 26	Philadelphia	digphilly.com	82	4	12.00	Debit
2	Fleetwood Mac	Rumours	May 4	Los Angeles	onlineseats.com	65	2	0	Debit
3	Sheryl Crow	Wildflower	April 18	Denver	ticketsnow.com	110	6	14.65	Credit
4	Blind Boys of Alabama	Higher Ground	May 4	New Orleans	ticketsnow.com	75	10	0	Debit
5	Beatles	Sergeant Pepper	April 29	Boston	concerttickets.org	80	3	27	Check

1. Browse Mode:	Ticket Price less than $65 with no processing fee
*2. List Mode:*	Concerts after April 26 that are not in New York or concerts with more than four tickets purchased and paid by Debit with a total cost no higher than $200
*3. List Mode:*	Concerts in Boston no later than April 20 with at least six tickets purchased or no more than four tickets purchased by Credit with no processing fee