1 / 7

Queries Exercise

Queries Exercise. Save and Open Baseball_Statistics.accdb Create the appropriate relationships for the following tables. Enforce referential integrity and cascade updates, but not cascade deletes. a. Statistics and Teams b. Teams and Divisions c. Teams and Leagues. QueryA.

merle
Download Presentation

Queries Exercise

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Queries Exercise • Save and Open Baseball_Statistics.accdb • Create the appropriate relationships for the following tables. Enforce referential integrity and cascade updates, but not cascade deletes. a. Statistics and Teams b. Teams and Divisions c. Teams and Leagues

  2. QueryA • Create a new query. For each team, list its full team name, full league name, and sum the total number of wins, losses, runs scored, and runs allowed. Sort by total number of wins in descending order. HINT: This query returns 30 records.

  3. QueryB • Create a new query. List each full team name and display the number of wins and losses of that team for the 2009 season. Do not show the season in your result. You should also include a calculated field that determines the win percentage of each team. • You can calculate the win percentage of a team using the formula:

  4. QueryBContd • Format the win percentage as a fixed type number with 3 decimal places. Sort by win percentage in descending order. • HINT: Your query should return 30 records.

  5. QueryC • Write a new query that for each division and league calculates the average number of runs scored and the average number of runs allowed in 2009. • Format the average runs scored and average runs allowed as a fixed type number with 3 decimal places. • Sort by average runs scored in descending order. HINT: Your query should return 6 records.

  6. QueryD • Create a new query that determines if each team has a winning or losing season. Using only the results of QueryB as input, show the full team name and win percentage of that team. • Include a calculated field using the IIF() function that indicates if a team had a winning season (won at least 50% of games) or not. If a team had a winning season, output Winning Season ; otherwise, output Losing Season . • Sort by team name in ascending order. HINT: Your query should return 30 records.

  7. Reports • Let us put things into a visually professional format suitable for printing • Creation can be somewhat like doing Forms with Subforms…. • Using the REPORT WIZARD, create a report to show the results of QueryA. Display all fields from the query. • Group by league name and sort by team name in ascending order. Use a stepped layout, landscape page orientation, and a design style of your choice. Name the report LeagueSummary .

More Related