Access Import Homework - DePaul University



LSP 121

Homework #1

Due: Wednesday September 12th

1. You are working at “the Ray” and have been asked to create a new activity called “Bike the Loop” in which students will once a week ride their bikes to an unusual and historic location somewhere in Chicago’s loop. In order to better manage who signs up, you want to create a single-table database. You will record the following information: Last name, first name, student ID, phone number, own bike?, beginning date, ending date, and amount paid. Make sure you create a primary key. You will also (in the future) need to total all the amount paid fields. You can use the following data:

Roberts, Julia, 1234567, 312 362-5175, Y, 3/20/12, 6/6/12, 10.00

Grant, Hugh, 2345678, 312 362-3344, Y, 3/30/12, 6/6/12, 10.00

McCabe, Richard, 3829113, 312 362-9012, N, 3/30/12, 5/9/12, 20.00

Ifans, Rhys, 9302342, 312 362-1193, Y, 4/14/12, 6/6/12, 0.00

Dreyfus, James, 1011223, 312 362-8124, Y, 3/20/12, 6/6/12, 10.00

Moran, Dylan, 1010111, 312 362-7871, N, 3/20/12, 6/6/12, 10.00

Frost, Roger, 1230012, 312 362-1099, Y, 3/20/12, 6/6/12, 10.00

Goodman, Henry, 1230033, 312 362-8721, Y, 3/20/12, 6/6/12, 10.00

a. Once the data is entered, copy and paste the table into your Word document.

Perform the following queries. Copy the Access query results into your Word document:

b. List last name, first name, and phone number of each student who has their own bike.

c. List last name, first name, phone number, and ending date of each student who cannot stay in the class until 6/6/12.

d. List last name, first name, and phone number of each student who has not yet paid.

2. Go to the QRC website (qrc.depaul.edu) and right-click on the file Actors.txt and Save Link As (Click on the LSP 121 link on the left side of the QRC website and then look near the bottom of the page for the file Actors.txt.) Save this file either to My Documents or to your flash drive. Open Access and create a new blank database, naming it Homework 1. Then click on the External Data tab near the top of the screen and then select Text File. Import the file Actors.txt into a new table. Follow the Import Spreadsheet Wizard and answer the wizard’s questions accordingly. (Use the comma delimeter.)

Note: the text file is very large but not complete. There are numerous actors and actresses missing, but that’s OK. We’ll still have fun looking for some data. The first row does not contain column headings, import into a new table, don’t worry about creating indexes, and use the first field as the primary key.

Once the spreadsheet has been imported into the Homework 1 database, be sure to check the file xx$_ImportErrors (if one exists) and address any errors that occurred during importing.

If you are satisfied that the data was imported correctly, perform the following operations:

a. Using the Find operation (under the Edit pull down menu, or simply enter Ctrl-F), how many movies did the actor Peter Sellers make?

(As you can see, the Find operation is not always the best tool to use when looking through a database.)

b. Using the Filter operation, filter all records for all actors in the movie Casablanca. List these actors to your Word document.

c. Using a query command, find all the actors in the movie Casablanca. Which was easier – filter or query?

d. Who is your favorite actor/actress? Using a database query, what movies did that person star in? (If your favorite actor is not listed, try another.) List the actor’s name and the movies to your Word document.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download