To Activities

LSP 121 -- Activity 9

Importing Spreadsheet Data

1. Download the Excel file StateTemperatures.xls from the QRC website qrc.depaul.edu to your harddrive or memory stick.

2. Create a new Access database with the .accdb extension.

3. Import the worksheet named Data from StateTemperatures.xls into the Access table Temperatures. Make sure that the checkbox "First Row Contains Column Headings" is checked. Also choose the option No Primary Key.

4. What field or combination of fields should you use for the primary key? (Remember that the values of the primary key must be unique.) Set the primary key to this field or fields to see if your answer is correct. Explain what happened if or when you made the wrong choice.

5. After the spreadsheet has been imported into your database, check the file xx$_ImportErrors (if one exists) and explain any errors that occurred during importing. Fix these errors in your Access file

6. When you are satisfied that the data has been imported correctly, answer the following questions:
   1. Using the Find operation, were there any states that had an average temperature of 90 degrees? If so, list the state name, the month, and the year where it occurred in. (Note: Look for 90.00, not 90 or some other variation; Look In the entire table; and Match the whole field.)

   2. Using the Find operation, were there any states that had an average temperature of 85.00 degrees? If so, list the state name, month, and year.

   3. Using the Find operation, were there any states that had an average temperature of 0.00 degrees? If so, list the state name, month, and year.

   4. Using the Filter operation, filter all records for all states for the year 1954. Select the first 10 records and copy them into your Word document. Show the Advanced Filter/Sort that you used for this question in a Word table.

   5. Using the Filter operation, find all states and years where the average temperature in January was greater than the average temperature in April. Show the Advanced Filter/Sort that you used for this question in a Word table.

7. Download Book Stores.xls from qrc.depaul.edu.

8. Import the worksheet CA (Califoria) into your database as the table BookStores. The first row contains the column names. Don't worry about creating indexes or a primary key.

9. When you are satisfied that the data was imported correctly, answer the following questions:
   1. If you wanted to create a primary key, could you have used the field StoreID? Explain. Set StoreID as the primary key to see if your answer is correct. Explain what happened.

   2. After the spreadsheet has been imported into the Activity 1b database, check the file xx$_ImportErrors (if it exists) and address any errors that occurred during importing.

   3. How might you correct the import error(s)?

   4. Use Find to find all the bookstores in the city San Jose.

   5. Now use Advanced Filter/Sort to find all the bookstores in the city of San Jose. Show the Advanced Filter/Sort in a Word table.