Query and Report Activities

This activity was created by Dr. Andrea Peach for use in her EDU542 class. It makes use of a rather large dataset. As you work through this exercise, you should come to understand the power of a database and the limitations of a spreadsheet when it comes to answering questions about a dataset, especially if you wish to deal with a subset of the data. In addition, you will get practice creating queries and reports using real data.

Accessing the Online Dataset

We will be using nutritional data about cereals from a file that is posted on the internet. The file may be found at the http://lib.stat.cmu.edu/DASL/Datafiles/Cereals.html

The data begins after the 16 items identifying the individual columns of data. You will know that you have found the data when you see the heading The Data!

• SELECT the data
  • starting immediately to the left of name, click and drag until all data has been selected
  • the screen will scroll until you have reached the bottom
• Then choose Edit  Copy (Ctrl-C).

Pasting the Selected Data in Microsoft Excel

Now, let's Paste the data somewhere. 

• Open Microsoft Excel
• Click in cell A1
• Choose Edit  Paste Special
  • you will have to use Paste Special because the data was copied from a web page
  • click on either Unicode Text or Text in the Paste Special Dialog Box 
  • click
• the data will now be in an Excel spreadsheet
  • note that row one contains the column labels, and these labels correspond to the data labels on the web page
  • note that there were 16 items in the data set, and there are 16 columns in the spreadsheet

Ok, we have the data now. What can we do with this data?

• Identify at least five provocative and interesting questions which might be explored using this collection of data. Now, let's see if we can find the answers in Excel
• What can you do in Excel?
• What can't you do in Excel?
• You need a database!

Importing the Data into Access

So, let's put this data into a database.

• Save the Excel file on your Y drive
• Open Microsoft Access
• Choose “Blank Access Database” and name it cereals.mdb
• Save it on your Y drive
• Choose File  Get External Data  Import
• Change “File Type” to Excel in the Import dialog box 
• Find the Excel file that you saved previously, click it, then click <Import> button
• In the next dialog box, click Sheet 1 next to “Show Worksheets”, then click
• In the next dialog box, make sure to check First Row Contains Column Headings, then click
• In the next dialog box, click In New Table, then click
• In the next dialog box you don't need to change the field types, so just click NEXT again
• In the next dialog box, choose No Primary Key, then click
• In the next dialog box, enter Cereal Data for the name of the table then click  
• You will receive an acknowledgment that the import of the data was successful
• Click  
• Double-click on the table that you just created. You should see all the data. What does this look like?
• Close the table

Querying the Cereal Database

Take one of your questions that you wrote above. Now ask that question in the form of a query. Try several queries that involve asking specific questions in design view.

Generating Reports

Now generate a report using each of the queries you designed.

Take a Look at the Other Data Sets Posted

There are a number of other data sets posted at this site. Go to http://lib.stat.cmu.edu/DASL/allsubjects.html to view the list of data sets by subject. Pick a subject and explore the data set.

Copyright © 2003
last updated August 13, 2003