Returns exercise

This assignment is intended to provide some exposure to Access95 (e.g., Microsoft Access version 7.0) and to illustrate some basic concepts used in relational databases, including data tables and queries. It also demonstrates how integrating existing software packages enables you to build information systems on a nearly ad-hoc basis.

The Scenario

You are starting a small mail order company, and have shipped your first two orders. Unfortunately, your customers discovered that your products were the wrong size, so they returned them. You need to send them letters saying that you have credited the appropriate credit card accounts for the amounts you previously charged.

Here are the customers' names and addresses:

Last name	Envelope name	Dear	Street	City	State	Zip
Jones	Mr. Jim Jones	Mr. Jones	27 Appian Rd.	Northridge	CA	90132
Osborn	Prof. Charles S. Osborn	Charley	Babson Hall 324	Babson Park	MA	02157
Smith	Ms. Jean Smith	Ms. Smith	34 Beech St.	Belmont	MA	02178

Here are the products they ordered:

Last name	Product name	Price	Shipping	Total	Card
Jones	Epoxy bed canopy	$3,000	$20	$3,020	Mastercard
Smith	Survival dry suit	$3,000	$100	$3,100	Visa

Doubtless you would like to prevent having to retype all of this data into each of three letters. After all, 50 people have placed orders since these returns arrived and you have to pack all 50 at your kitchen table.

Fortunately, you thought ahead and listed the customers names and their orders in two Excel spreadsheets. Those are available in a file named 1retdata.xls. You may want to download it prior to beginning work on the assignment.

You also have a draft on file of the letter that you want to send to each customer thanking them for their order and telling them that you have credited their account for the appropriate amount. This is in a file named 1retltr1.doc (you've been using particularly catchy file names lately).

In this letter, you've included places where the text might vary. At each place you've included notes to yourself enclosed in [square brackets]. These notes describe what information you want to include at that position in the letter.

In your file, the letter looks something like this:

The Returns letter

[Date: Have to include the date here. Word provides speedkeys for this: alt+ i + t]

[Customer's envelope name
Customer's street
Customer's city, state, zip - use database fields for these lines]

Dear [name - use the "Dear" field for this]:

Thank you very much for your recent order of the [product]. We look forward to replacing it with another that better meets your needs [or some other suitable thank you/we're sorry phrase].

We have credited your [credit] card in the amount of [total], which represents the product's price of [price] and shipping charges of [shipping].

Thank you for ordering from Only Essentials, Inc. Please let us know how else we can help you by calling 1-800-ALL-NEED.

Sincerely,

[your name, etc.]

The Assignment

1. Create new Access tables named Customers and Orders from the data in the excel file 1retdata.xls (to save time, you can paste the data into Access from Excel).
2. Build a query in Access that describes who ordered what.
3. Use the Returns letter in the Word file 1retltr1.doc to develop a letter that you can send to customers who have returned merchandise. Use mail merge to make it into a database report that generates letters from the data in your Access query.
4. Prepare, print, and hand in the assignment deliverables.

Deliverables

1. A cover letter, based on the model shown below and available in Word file 1retltr2.doc. Your version will include screen snaps of each of your Access tables. It will include your name and contact information in the stationery header. It will also include a brief description of questions, problems or comments you developed during the assignment. These will remain confidential.
2. Stapled to it, a copy of the Returns letter personalized for each customer who has ordered and returned product.

The assignment cover letter

Please note that this cover letter asks you to build a new mail merge file based on the Customers table in your database. This is to give you an additional opportunity to familiarize yourself with setting up a mail merge should its procedures be new to you.

The suggested text of the cover letter is as follows [variable text shown in brackets]. A full copy is available in 1retltr2.doc so please don't waste time retyping anything.

[Date - try using Alt + i + t ]

[Osborn's name
Osborn's office address
Osborn's city, state, zip - use database fields for these lines]

Dear [name - use the "Dear" field for this],

Here are my results from the first homework assignment. I built the following tables in Access:

[place tables or screensnaps here]

They resulted in the letters which are stapled to this cover page.

I had the following questions after completing the assignment:

[please include questions here]

I have the following suggestions for improving this exercise, for the following reasons:

[please include any feedback you wish to provide here]

Sincerely,

[etc.]

Files

The following files are used in this assignment:

Filename	Description
1retdata.xls	Excel spreadsheet with data for Customers and Orders tables in Access.
1retltr1.doc	Word document with preliminary draft of Returns letter.
1retltr2.doc	Word document with preliminary draft of cover letter.

Feel free to download them and use them as you wish. They are intended to save you typing time. Why type when you can copy and paste?

n.b. The cover letter file, 1retltr2.doc, has a header that makes passable Babson stationery should you need to fax or mail pages to outside contacts during an upcoming job search.