IT 121 - DePaul University



LSP 121-405

In-Class Activity 1

Importing Spreadsheet Data into a Database

20 points

Due by Saturday, Sept. 19th 11:59 pm

Most database programs such as Microsoft’s Access allows you to Import data from an external file. Go to the QRC website (qrc.depaul.edu), click on Excel Files and right click on the file StateTemperatures.xls. Save this file either to My Documents or to your flash drive.

Open Access and create a new blank database, naming it Activity 1a. When Access 2007 creates a new blank database it automatically opens a window for creating a table. We don’t need to define this table right now so let’s close this table – click on the X in the upper right corner of the Table window - middle-right of the screen. Then click on the External Data tab near the top of the screen and then select Excel. Import the Excel spreadsheet StateTemperatures.xls into a new table. Follow the Import Spreadsheet Wizard and answer the wizard’s questions accordingly:

a. The spreadsheet has multiple worksheets (Data and Background); select the Data worksheet

b. First row will contain column headings

c. Don’t create any indexes

d. No primary key.

Once the spreadsheet has been imported into the Activity 1a database, be sure to check if a table named xx$_ImportErrors exists and address any errors that occurred during importing. If one is created, you will see it listed along with the Table names.

If you are satisfied that the data was imported correctly, then answer questions 1-6 on the answer sheet.

Go to the QRC website (qrc.depaul.edu) and click on the file Book Stores.xls. Save this file either to My Documents or your flash drive. Open Access and create a new blank database, naming it Activity 1b. Then import the spreadsheet using the same steps we did above. Follow the Import Spreadsheet Wizard and answer the wizard’s questions accordingly.

Note: the spreadsheet has multiple worksheets (various states). Import the data from the CA (California) worksheet only. Import into a new table, don’t worry about creating indexes, and don’t create a primary key.

Now answer questions 7-10. Submit your answers to the In-Class Activity #1 assignment in the course COL web site.

LSP 121-405

In-Class Activity 1

ANSWERS

Student Name(s):

Answer the following questions by typing into this Word file, save the resulting file and submit it as assignment “In-Class Activity #1” on the COL web site by Sept. 19th. You may do this assignment alone or in a pair. No more than 2 students can submit the same answer file.

1. If you had wanted to create a primary key (as opposed to letting Access create it for you), is there any field that you could have used? Explain. Remember: the values in a primary key field cannot repeat.

2. Using the Find operation (back under the Home tab, or simply enter Ctrl-F), were there any states that had an average temperature of 85.00 degrees? If so, list the state name and the month it occurred in. Don’t be surprised if there are none. (Note: Look for 85.00, not 85 or some other variation; be sure to Look In the entire table; and Match the whole field.)

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

5. Using the Filter operation, filter all records for all states for the year 1954. Select the first 10 records from this list and copy them into your Word document here (this can be easily done with Copy (CTL-C) and Paste (CTL-V) commands).

6. Continuing with the data for 1954 only, use the Sort operation on the March column (highest to lowest) to determine which state had the highest average temperature in March, 1954. Select the first 10 records from this list and copy them into your Word document here.

Questions 7-10 are to be answered with data from the Book Stores.xls data.

7. Once the spreadsheet has been imported into the Activity 1b database, be sure to check the table CA$_ImportErrors. There was an error in importing some data. Use MS Excel to examine the original BookStores.xls file and find the source of this error. Why do you think this error occured? How might you correct the error? You don’t have to correct the error, just describe in a sentence or two how you might do it.

8. Question: If you wanted to create a primary key, could you have used the field StoreID? Explain. Be careful! You better look at the entries before you answer.

9. Now use Filter to find all the bookstores in the city San Jose. Type the names of these bookstores below.

10. Remove the filter from question #9. Now apply a new filter that selects all cities with names that begin with “San”. Select these records from the Access screen and copy them here.

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

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

Google Online Preview   Download