To Activities

LSP 121 -- Activity 10

Advanced Queries

• Part a: Use the database real-estate.accdb to answer the following questions below

• Submit the modified Access file with your saved queries. Do not submit a Word file.

• The real-estate.accdb database contains four tables. Here are the tables with their column names:
  Listings:   Listing Number (PK), Address, City, State, Zip, Number of Stories, Frame Type, Basement, Heating Type, Central Air, Taxes, School District.

  Agents:   Agent ID (PK), Last Name, First Name, Address, City, Phone Number

  Agent Listing:   ID (PK), Agent Code, Listing Code

  Rooms:   RoomID (PK), Room Width, Room Length, Floor Type, Type of Room, Listing Code

  Important reminder: When constructing a query, only include those tables that will be necessary to perform that one query. If you include unnecessary tables, unpredictable results may occur.

  Use Queries to answer the following questions. Save the queries with names Query1, Query2, ... , Query8, corresponding to the question being answered. For each question, show the answer to the question, the result table and the Access query. Show the Query in a Word table.

  1. Compute the count of how many brick houses and how many frame houses there are currently on the market.

  2. Compute the average width and average length of all rooms in all listings. Note that not all listings even have room information. You are computing the average width and length for the rooms that are listed.

  3. Calculate the average width and average length of each type of room. (Group by Type of Room.)

  4. Compute the average width and average length of all bedrooms in all listings.

  5. Compute the count of how many homes have Gas FA (forced air), Gas Boiler, Electric, or Solar heating. You can use the trick described in Section L, Subsection Queries with Summaries or Groupings.

  6. What is the highest property tax and what is the lowest property tax for all the listings in the database.

  7. What are the highest and lowest property taxes in River Forest?

  8. Create a Parameter Query which prompts for an agent's last name and then displays the street address for each listing for that agent. (The query will require three tables: Listings, Agents, and Agent Listing.) See the Access Tutorial,i the end of Section L to see how to construct Parameter Queries.

• Part a: Import the data from the text file Actors.txt into a table in an Access database. This table contains information about movies and the actors/actresses in them. The information in the table is not complete, but it contains some interesting information.

  For each question, show both the answer and the query that you used in a Word table. Do not submit the actual Access database, because it is very large. Write the queries in Query Design view.

  1. As a group, choose your favorite movies, one per group member. Write a query to find the actors/actresses in these movies. Show the query and the answer table in your Word document.

  2. As a group, choose your three or four favorite actors/actresses. Write a query to find the movies that they are in. Show the query and the answer table in your Word document.

  3. Which actor/actress starred in the most movies? Create an aggregate to show the count of movies, query grouped by actor, and sorted by descending count.

  4. Which movie used the most actors/actresses.

  5. Write a parameter query that lets you look up the movies for any actor/actress. Show the professor in the lab to verify that it works. Show the query in your Word document.