To Activities

ISP 121 -- Activity A2

Importing Spreadsheet Data

Important:   One person in your group should type your answers, for what's asked for in Parts I, II, and III below, into an MS Word file and submit it using the COLweb system. Make sure the Word document has all group member names listed at the top.

Most databases are constructed of multiple tables and joined using relationships, it is also quite common to have a simple, flat set of records in some other format such as a text file (.txt) or an Excel file (.xls) and wish to manipulate these records via a database. Most database programs such as Microsoft’s Access allow you to Import data from an external file. That's a lot of what this activity is about.

Part I

  1. Download the Excel file StateTemperatures.xls to your harddrive or memory stick.

  2. Create a new Access database named A2p1.mdb.

  3. Import the worksheet named Data from StateTemperatures.xls into an Access table named Data.

  4. Now open up the Data table and create (from the existing fields) a primary key for the table. What field(s) did you select as your primary key? Explain why you selected that as your primary key.

  5. When you are satisfied that the data was imported correctly, answer the following questions:

    1. Using the Find operation, were there any states that had an average temperature of 88.1 degrees? If so, list the state name and the year and month it occurred in. 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 degrees? If so, list the state name and the year and month in which it occurred.

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

    4. Using the Filter operation, filter all records for all states for the year 1954. To perform this filter, locate a field with the value 1954 and highlight it, then Filter By Selection. Select the first 10 records and copy them into your Word document.

    5. Use an Advanced Filter to find all temperature data for Illinois and California for the years 1901, 1932, 1942, and 2000. Copy the result into your Word document.

Part II

  1. Download the Excel file BookStores.xls to your harddrive or memory stick.

  2. Create a new Access database named A2p2.mdb.

  3. Import the worksheet CA (California) into A2p2.mdb as the table CAbookStores. The first row contains the column names. Don't worry about creating indexes or a primary key.

  4. 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.

    2. After the spreadsheet has been imported into the database, check the table xx$_ImportErrors (if it exists) and explain why the error(s) occurred. How might you correct the import error(s)? You don’t have to correct the error, just tell me how you might do it.

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

    4. Now use Filter to find all the bookstores in the city San Jose.
      • Copy the result of your filter into your Word document.

    5. We wish to generate a list of bookstores in a certain region of California.
      Use Filter by Form to find all bookstores having zip code beginning with 94.
      • Copy the result of your filter into your Word document.