ISP 121 Activity 9

Importing, Finding, Filtering, and Sorting

Open the Excel file flights.xls  The file contains data on flights into and out of various airports, the days of the week, the departure time, the arrival time, and the price.  The following airport codes are used: DFW=  Dallas; JFK= New York City; LAX = Los Angeles; MIA = Miami; ORD = Chicago; SEA = Seattle.  The departure and arrival times are given in military time. 

Save the file to the desktop.  Open and create a new Access database. Save your database to the Desktop.  Name it Flights and save it to the desktop.  Import the data in the excel file into a new Access database.  You do not need to add a primary key.  Name the table Flights. 

Open the Flights table in Flights.mdb.  Use the Find, Sort, Filter, and Advanced Filter operations to answer the following questions.

1.  If you wanted to create a primary key for this table, is there a field or combination of fields that would work?  Explain your answer. 

2. For each of the following questions, state which function you used (Find, Sort, Filter by selection, or Advanced Filter) and explain the criteria for the operation.  Also write the answer in your word document.

  1. Name 2 flights in different cities (departure city, arrival city, and day of week) that leave at 8 am.
  2. Name 2 flights in different cities (departure city, arrival city, and day of week) that arrive at 5:30 pm.
  3. What are the most expensive flights?  What is the price?
  4. What is the least expensive flight that arrives in Chicago?
  5. How many flights originate in New York and arrive in Miami?
  6. Create a table with all flights that leave after noon on Tuesday.  Copy and paste the table into your word document.
  7. You are flying from Dallas to Chicago on a Friday.  You want to get there as early as possible to enjoy the city, but can't leave until after 11 am.  What is the earliest arrival time for a flight from Dallas to Chicago that meets your needs? Name the time of departure and time of arrival.
  8. Your movie star friend lives in Los Angeles and is doing a play in New York.  He wants to fly home Sunday evening after the performance.  Find the flight(s) that would work for him if the earliest he could leave on Sunday is 8 pm.  State the departure time and arrival time. 
  9. Come up with your own question to ask about the flights in the table and answer it. 

 

Open the Excel file Bookstores.xls.  Import this file in an Access database. 

3.  When you import, you will receive an error message if you choose Store ID for you primary key.  Look carefully at the data, and explain why this occurs.  How would you fix this error?

For each question, state which function you used (Find, Sort, Filter by selection, or Advanced Filter) and explain the criteria for the operation.  Also write the answer in your word document.

  1. How many stores have "books" or "bookstore" in its name?  (Hint use the Find operation>>Find what: books>>Look in: Store Name>>Match: any part of field).
  2. How many stores are in San Francisco?
  3. How many stores have a 415 area code?
  4. Create a table of the stores in NY sorted ascending by postal code.  Copy and paste the resulting table into your word document. 
  5. Are there any stores in the list that are in DePaul's zip code?  (our zip code is 60614)
  6. Come up with your own question to ask about the bookstores in the table and answer it.