ENV* K105

Assignment #9--Starting with Microsoft Access
Due 11/15/05 (Home)

Over the next three classes, we will cover Chapters 33 through 35 of Special Edition Using Microsoft Office XP, which is on reserve in the Thames Valley library. We are using Access 2003, which operates pretty much the same as XP, with some minor considerations discussed below. You should also look at my brief lecture on Access.

There are a number of sample databases that comes with Office 2003. These databases may be in the folder C:\Program Files\Microsoft Office\Office11\Samples\ where "C:\" is assumed to be your hard drive. If these are not installed, you can install them using the Microsoft Office 2003 setup disk. Search Access Help using the term "northwind traders" for more info on the sample database.

You should be aware of some important differences between MS Access database files and other Office 2003 files (such as worksheets). A Excel workbook or a Word document can be opened and fiddled with and then closed without saving any changes. This is not the case for a database. Once you open the file, Access saves almost every change automatically. For this reason, if you want to mess around with a database that should not be tampered with, create a copy with a new name and open the copy. For example, if you want to keep an original of the Northwind Traders db, make a copy. Alternatively, you can right click on a database filename in Windows Explorer and choose "new." Access will create a copy and add the number 1 to the name of the file.

Also, while Excel 2003 and Word 2003 files can be opened by Office 97 applications, Access 2003 database files cannot be. If you open an Access 97 database in Access 2003 (following a lengthy conversion process), it will no longer be readable by Access 97. You can view data in an Access 97 (or earlier) database without converting it. It is also at least theoretically possible to save an Access 2003 database as an earlier version. You can also import and export data back and forth between 97 and 2003 databases. Search on "Convert an Access file" in Help for more information. I believe that the file format is the same for Access 2000, XP, and 2003. At least the files from one can be read by the others.

Microsoft Access comes with a number of wizards that can help you create a database to keep track of things such as your friends, your album collection (do people still have album collections?), your household goods, or a club membership list. One wizard allows you to create a database to track information about an event like a convention or trade show. These wizards are fairly flexible and allow you to customize the database as you create it. Also, you can create a new database with sample data thrown in by Access so that you can see how the pieces fit together. To use a wizard, simply start Access. Then, select General Templates under New from template on the Task Pane.

In this assignment, you will learn about what the types and parts of a database, and the basics of creating, opening, and saving database files. You will learn about database tables (Chapter 33), and you will learn how to extract information from databases using a query (Chapter 35). You will import data to start a database of your own. Next week you will learn about forms and reports.

0. Read this entire assignment.
1. Copy the Word document "addresses.doc" to your I:\ drive (click here for info on how to copy a Word file from the Web). You may recognize the data from our adventures in mail merge.
2. Copy the Excel workbook "dues2005.xls" to your I:\ drive (click here for info on how to copy an Excel file from the Web).
3. Open addresses.doc. Add a row to the table with your name and address, and then save the file. Prepare the file for import:
1. Select the table and convert table to text. Separate text with tabs.
2. Save the file as a text only file named "addresses.txt". Close Word.
4. Open dues2005.xls. Add your name and a paid amount of $1.00. Save and close Excel.
1. Create a database and import some data, then examine the data using queries:
1. Start Microsoft Access. Create a new, blank database by selecting Blank Database under New on the Task Pane. Name the new database "ABC_trcecc.mdb", where ABC is your initials (and trcecc stands for Three Rivers Civil and Environmental Computing Club).
2. Import the data into a new table.
1. Under File, choose Get External Data►, Import...
2. Look in your I:\ drive. Select Files of type: Text Files. In the file display, the file "addresses.txt" should appear. Select it and hit Import.
3. Follow the instructions of the import wizard.
• Be sure that the delimited format is selected.
• Choose tab as the field that separates your fields. Check "First Row Contains Field Names".
• Store the data in a new table.
• When you get to the screen where you can skip fields or change field data types, scroll over to the "Zip" column and change your data type to "text". Otherwise, your zip codes will lose their leading zeroes (eg, "06360" will become "6360").
• Let Access choose the primary key. When you finish, you have yourself a new table in your database.
2. Link a table with your new database:
1. Create the link.
1. Under File, choose Get External Data►, Link Tables...
2. Look in your I:\ drive. Select Files of type: Microsoft Excel. In the file display, the file "dues2005.xls" should appear. Select it and hit Link.
3. Follow the instructions of the link wizard.
• Check "First Row Contains Column Headings".
• Change the name of the new table to "dues2005".
2. Check this out:
1. Open the worksheet "dues2005.xls" and change the dues you paid to $100
2. Go back to Access. Look at what happened in the linked table.
3. Create a Query.
1. Create the new query.
1. Select the Queries button under Objects on the left side of the database window.
2. Double click Create query in Design view.
3. The Show Table dialogue box will appear. Add both tables then hit Close.
2. You will need to create a relationship between the two tables.
1. In the little box labeled "Addresses", click and hold on the field "Last_Name". Without releasing the mouse button, drag the cursor over to the field "Last_Name" in the dues2005 box. A line indicating a link between the two fields should appear. If you get some other line, click on it and hit delete. Try again.
3. Build the query.
1. Click in the first column of the field row of the bottom half of the design query window, and select "Address.First_Name" from the drop down list. Notice that the table containing the field is given before the "." and the field name is given after. The table name shows up in the Table row.
2. In the next column, select "Addresses.Last_Name". In the Sort row, select "Ascending".
3. In the next column, select "dues2005.Dues Paid 2005". In the Criteria row type ">.25".
4. Run the query.
1. From the query menu, choose Run or hit the run button (with the big red ! on it).
2. Check out who's paying.
5. Print the query.
1. Choose File, Print...
6. Close the query by clicking the X in the top right corner. Click Yes when you are prompted to save. Name the query DuesOwed.
4. Modify the query by adding a calculated field:
1. Open the query in Design View.
1. With the database window showing, select the Queries button.
2. Select DuesOwed; then click the Design button on the toolbar at the top of the Queries window.
2. Create the calculated field to show the amount of dues owed for the year. Suppose that the annual dues for the Civil and Environmental Computing Club is $1.00. Then the dues owed would be given by the following formula:

Amount Owed = $1.00 - Dues Paid

Put this formula into the query as follows:

1. In the Field row of the first blank column, type "Amount Owed:" (don't type the quotation marks, but do include the colon).
2. Right click on the cell and choose Build.... The Expression Builder will open.
3. The Expression Builder helps you to put together a formula or other expression for a calculated field. In the top of the dialogue box, the expression is displayed. So far it says only "Amount Owed:".
• Type an equal sign. Calculated expressions should start with an equal sign.
• Type the number 1 and a minus sign.
• The lower half of the Expression Builder is listing of all available fields and pre-built formulas. The left column shows the current query open. The middle column shows the fields that are available in the current query.
• Just for practice, click on the plus sign next to Tables in the left column. You'll see a list of tables available in this database. Click on one of them. The middle column of the Expression Builder will show the fields available in that table.
• Click back on DuesOwed at the top of the left column. Double click on Dues Paid 2005 in the middle column and click OK.
• You return to the Design View of your query. If you want to see the whole expression in the field cell, hit SHIFT+F2 to get a Zoom window.
• In the Sort box in this column, choose Descending. In the Sort box of the Last_Name column, choose (not sorted).
4. Go to the Dues Paid 2005 column. Delete the criteria. Uncheck the Show box.
5. Run the query. You should get a list of members with the amount of dues owed, sorted in decreasing order of amount owed.
6. Print this query.
7. Close Access. Select Yes when prompted to save the changes to the query.
5. Use WebCT to send me your *.mdb file and your linked Excel file. Please put them both into a single zip folder.