YO16_AC_CH03_GRADER_PS2_AS - Baseball Academy Project Description: Matt Davis is a retired baseball player who runs the Baseball Academy, an indoor baseball facility for middle school, high school, and college players. He offers lessons as well as practice times for individuals and teams. Due to his growing clientele and increased record-keeping needs, Matt wants all his records in a database. While he has already set up a database, he now needs to take the database to the next level of performance by improving his ability to get specific data out of the database. He has hired you to create the queries he will need to get this information. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible

1 Start Access. Open the downloaded Access file named a02ch03_grader_h1. Save the file with the name a02ch03_grader_h1_LastFirst, replacing LastFirst with your name. In the Security Warning bar, click Enable Content. 0

2 Open tblMember. Add a new record with FirstName Access, LastName Student, Address 101 Main Street, City Santa Fe, State NM, ZipCode 87594, and Phone (505) 555-1010. Close the table. 4

3 Modify tblPayments to have a font type of Arial and a font size of 14. Autofit all fields. Save and close the table. 5

4 Using the Find Duplicates Query Wizard, create a query that will show members who have the same address. Show all the available fields in the query. Name the query qryDupAddresses_aStudent. Save and close the query. 7

5 Using the Find Unmatched Query Wizard, create a query that will show anyone who is listed as a member in tblMember but has not made a payment, match on MemberID, and return the following fields: LastName, FirstName, Address, City, State, ZipCode, and Phone (in that order). Name the query qryNonPayment_aStudent. 8

6 In Design View, sort in ascending order by LastName and then FirstName. Save and close the query. 2

7 Using Query Design, create a query that returns members' LastName, FirstName, Amount, and PaymentDate (in that order). Sort in ascending order by LastName and then FirstName. Switch to Datasheet View and add a total row to show the sum of payment amounts. Name the query, qryPayments_aStudent. Save and close the query. 15

8 Using Query Design, create a query that returns employee FirstName, LastName, Position, and Salary (in that order). Select only those that have the word Instructor in their position. Sort the results by salary (highest to lowest) but do not show Salary in the results. Name the query qryInstructors_aStudent. Save and close the query. 15

9 Using Query Design, create a query that returns employees' FirstName, LastName, and Salary. Name the query qryRaises_aStudent. Add a new field to the query, which is salary * 1.03 and call the field SalaryWithRaise. Sort the results by SalaryWithRaise from highest salary to lowest. Switch to Datasheet View and add a total row to show total payroll before and after the raise. Autofit all fields. Switch to Design view. Format the SalaryWithRaise field as Currency with Auto decimal places. Save and close the query. 20

10 Using Query Design, create a query that returns the sum, average, minimum, and maximum of employee salaries (in that order.) Name the fields Total Salary, Average Salary, Minimum Salary, and Maximum Salary (in that order.) Autofit the column widths. Name the query qrySalaryStats_aStudent. Save and close the query. 12

11 Using Query Design, create a query that returns the member's LastName, FirstName, ScheduledDate, and Fee for any lesson between June 1 and June 30, 2018 that had a fee greater than or equal to $250. Sort in ascending order by LastName and FirstName. Name the query qryBigJuneFees_aStudent. Save and close the query. 12

12 Close all database objects. Close the database and then exit Access. Submit the database as directed. 0 Total Points 100