Confused (even slightly)? This file is intended to help. What exactly does one need to do to finish this dratted assignment? Main tasks and subtasks are listed below. They are described further, with some screen shots, in the rest of this file. Please note that some of the screen controls might look a little different from Windows95 screens, as these screens were captured from a machine running Windows NT workstation 3.51.
| Building database tables | Queries: data you want | Reports: merge w/Word |
|
|
|
The first job for this assignment is to build Customers and Orders tables in Access. The data have already been provided for you in an Excel file (1retdata.xls), so you can do the following:
Copy the data from Excel to Access
Download, save, and open 1retdata.xls. You don't have to save it with this cryptic name if you don't want to; just remember what you called it. Now you have Excel running with this file loaded:
Open Access, asking for a new database. Name the new database Returns if you want - Access will force you to name it.
In the main database window, tell Access you want to make a new table. Do this by selecting the Tables tab, then clicking the New button.
Tell Access you want to see the new table in Datasheet view. You should get a blank table that has "Field 1", "Field 2", etc., across the column headings.
Use Alt-Tab to go back to Excel (which is still open).
Highlight the data in the Customers list - this means rows
2 to 4 for columns A to G, not row 1. Row 1 has what Access
would call the fieldnames in it. We'll deal with these in a moment.
Right now you just want the data. With A2:G4 highlighted (i.e.,
the data in the list), copy it to the system clipboard, either
by using Ctrl+C, the toolbar button 
,
or Copy command from the Edit menu.
Use Alt-Tab to go to Access.
Select the first three rows of the Access table. Paste
in the Excel data.
Please note: selecting the rows here is important. If you only
select one cell in the Access datasheet (e.g., only one field),
Access will dutifully attempt to copy all of the rows from Excel
into that single field. If you select only one row, it will copy
only one row of the three you had selected in Excel.
Access should alert you that you are copying in three new records. Tell it not to worry (e.g., click on "yes").
Your three rows from the Excel spreadsheet should appear in the Access table. You may have lost the leading zeros from your zipcodes. If so, don't worry about it for now, but ask about it in class. The answer has to do with data types and we should talk about it briefly.
Now go to the column headings. Double-click each one to edit it. Change them to read the same as the headings in the first row of your Excel spread sheet (have you printed out the spreadsheet yet, so that it is easy to refer to?).
Close the table and give it the name Customers. Access will warn you that you have not defined a "primary key". Choose "No" for now; we will discuss this (much) more in upcoming weeks.
A new table will appear in the main database window under the Tables tab.
Now repeat these steps for the Orders data on the Orders sheet in the Excel file, building an Orders table with appropriate column headings (i.e., fieldnames) in Access.
Then close Excel (you're finished with that file), but leave Access open.
Now that you have two tables, one describing Customers and the second describing Orders, you need to combine customer addresses with order information to generate the Returns letters. To do this, you need to combine data from tables within the database. The more technical term for asking such a question of a database is a database query. So the next step is to define a query that shows address and order information for each customer who returned product.
Open a new Query Design window showing both tables
In Access, select the Queries tab in the main database window,
and click the 
button.
Select Design View for the new query (we will discuss the other options later).
You will be asked what tables you wish to show in the query design window. Select Customers and Orders in turn, using the Add button to place them in the upper half of the query window.
When you are done, your query window should look something like this:
Specify relationships and select fields
Now comes a part that is easy to show but hard to describe in text. You need to tell Access how it is supposed to match records across the tables. Note how each table has a field called Last Name. If you matched addresses and orders by customer's last name, you'd get the correct mailing address for each order, right?
Sooo, we'll tell Access to do this by: selecting the Last Name field from the Customers table, then dragging it over and dropping it on the Last Name field in the Orders table.
Think of yourself as pointing to the Customers table, then to the Orders table while saying, "Match records with this last name in the Customers table up with any records that have the same last name in the Orders table". The drag-and-drop accomplishes much the same thing.
Once you have done this, Access will show a relationship between these tables, based on last name, as a solid line between the related fields:
Once you have defined the relationship on which the query is based, all that remains is to tell Access what fields you want to see from each table. Do this by dragging fields down from the table(s) in the top half of the query design window and dropping them in the Field row of the grid in the bottom half. The picture below shows the window after the first three fields from the Customers table have been selected:
To build the query, you will want to select (e.g., drag and drop) all the fields from each table with the exception of the Last Name field from the Orders table. You don't need this, since you are already showing the Last Name field from the Customers table.
At any time during the query design process, you can use the Datasheet
View button 
on the toolbar to
see the results of your query. The query will not be saved, however,
until you save it and give it a name. You can switch back to
Design View when you are looking at query results by using the
Design View button 
on the toolbar.
Once you have specified the tables, relationships, and fields you want for your query, you can save and name the query for future use. You might use a descriptive name such as Orders by Customer. Save the query by closing the query window, just as you did earlier when saving tables.
When you are done, the Queries tab in the main database window should look something like the following:
Most database management systems can be summarized very simply: they take data input, process it, and produce reports as output. In this exercise, we'll use Word as a quick and dirty report writer by relying on its so-called mail merge features.
Merging with Word will enable us to use a stock letter whose text varies only according to the data captured in the Returns database.
Link query results with blank Word file
To set up the Word merge, open the Returns database in Access (it should still be open) and select the Orders by Customer Query under the Queries tab (i.e., just as in the screen shot most closely preceding this paragraph).
With the Orders by Customer query selected, click on the
Merge to Word button 
on the toolbar.
Select the "Create a new document" option.
Access will open Word, giving you a new blank Word page and a new type of toolbar. This toolbar manages the combination of data from the Returns database with text in the new document, based on the Orders by Customer query that you selected when you clicked on the Merge to Word button.
Now is the time to open the copy of 1retltr1.doc that you downloaded. Copy the text from that file into the blank Word document that Access opened.
Modify the text to include fields from the Returns database by
using the 
button on the Merge
toolbar. You will notice that clicking on this button shows a
list of the fields that you defined in the Orders by Customer
query. Selecting one field drops it into the current cursor position
in your letter.
You would use this list, for example, to build the address portion of the letter from a combination of Envelope Name, Street, City, State, and Zip (with appropriate spaces and carriage returns, etc.). Use the "Dear" field for the salutation, etc. In short, you are replacing items in the letter text that are indicated in [square brackets] with fields from the database.
If you want to see what the letter looks like with field contents
instead of field names, use the 
button on the toolbar to toggle back and forth between the two.
This way you can review the letter with data in it before you
merge or print it. By switching to field contents view and scrolling
through each record in the database, you can see how the text
will look for each of its variations.
Print the letters to a new file
You can print single copies of the letter at any time, but use
Merge to New Document 
to generate
a set of letters, one for each record in the Orders by Customers
query. This will produce one of the deliverables needed for the
assignment. Remember to save your file.
Repeat for Cover Letter, using Customers Table
To produce the cover letter, return to Access and select the Customers Table in the main database window. That should include the following steps:
If you have trouble producing screen snaps of your tables, please let me know and we will discuss it further in class.