Homework answers / question archive / Shelly Cashman Access 2019 | Module 10: End of Module Project 1 Competitive Swim Clubs Inc

Shelly Cashman Access 2019 | Module 10: End of Module Project 1 Competitive Swim Clubs Inc

MS Access

Share With

---

Shelly Cashman Access 2019 | Module 10: End of Module Project 1

Competitive Swim Clubs Inc.

Using SQL

GETTING STARTED

• Open the file SC_AC19_EOM10-1_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_EOM10-1_FirstLastName_2.accdb by changing the “1” to a “2”.
  • If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.
• Special Note: SQL Queries
  
  To ensure accurate grading in this project, use the conventions listed below when writing your SQL commands:
  
  · Do not enclose field names and table names in square brackets [] when creating and modifying these SQL queries. All field names and table names consist of single words and do not require brackets.
  · Use parentheses () in your SQL code only when specified.
  · All SQL commands should end with a semicolon (;).
  · Only modify the aspects of the SQL commands that are specified in the assignment steps.

• PROJECT STEPS

1. Competitive Swim Clubs Inc. is a national chain of swim clubs targeted to youth and adults who are or who want to be competitive swimmers. You are the regional manager for clubs located in the southeastern United States. You want to be able to use the extended data management capabilities through SQL.
   
   Create a new query in SQL View based on the SwimFees table. Use the asterisk (*) in the SELECT clause to add all fields from the SwimFees table and be sure to end the SQL statement with a semicolon (;). Run the query and confirm that there are six records in the query result. Save the query using SwimFeesQuery as the query name.
2. Corporate management is considering the purchase of another club in Georgia. Open the GAMembersQuery in SQL View and add criteria to find all members located in Georgia (GA). Run the query and confirm that there are 12 records in the query result. Save and close the query.
3. The CoachesQuery must be modified to include the FirstName field in the query results. Open the CoachesQuery in SQL View and add the FirstName field to the query. It should appear before the LastName field. Run the query and confirm that the results match Figure 1. There should be three columns and 21 records in the query result. Save and close the query.

4. Open the NCMembersQuery in SQL View and restrict retrieval to only those members who reside in the city of Charlotte within the state of North Carolina. Run the query and confirm that there are seven records in the query result. Save and close the query.
5. Open the SwimmerLevelQuery in SQL View and restrict retrieval to only those swimmers whose LevelID is equal to or greater than 4. Run the query and confirm that there are 25 records in the query result. Save and close the query.
6. Open the JoinClubsCoachesQuery in SQL View. Add a WHERE clause that joins the Clubs table and the Coaches table. The common field in both tables is ClubID. Run the query and confirm that the results match those shown in Figure 2. The query returns 21 records. (Hint: Your records may be in a different order.) Save and close the query.

7. Open the SCCountQuery in SQL View. Modify the SELECT clause to count the number of members located in SC. Assign the name SCMembers to the function result. Run the query and confirm that the results match those shown in Figure 3. The query returns a count of 12 members. Save and close the query.

8. Open the CoachesSortQuery in SQL View and sort the records in ascending order by the ClubID field. Run the query and confirm that the results match those shown in Figure 4. The query returns 21 records and the last record is for CoachID 133. Save and close the query.

9. Open the GroupbyLevelIDQuery in SQL View. Add the LevelID field to the SELECT statement before the COUNT function. Group the records by the LevelID field and then sort the records by the LevelID field in descending order. Run the query and confirm that the results match those shown in Figure 5. The query returns six records. Save and close the query.

10. Coaches regularly update the swim levels. To make the updates more efficient, open the Swimmer Master Form in Design View and the change the tab order so that users tab to the LevelID field immediately after the SwimmerID field. Open the form in Form View to confirm the tab order change, then save and close the form.

Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.