Shelly Cashman Access 365/2021 | Module 3: End of Module Project 1 | Competitive Swim Clubs, Inc.

Competitive Swim Clubs, Inc.
MAINTAINING A DATABASE

GETTING STARTED
• Open the file SC_AC365_2021_EOM3-1_FirstLastName_1.accdb, available for download.
• Save the file SC_AC365_2021_EOM3-1_FirstLastName_1.accdb as SC_AC365_2021_EOM3-1_FirstLastName_2.accdb
o Edit the file name by changing “1” to “2”.
o If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.
• With the file SC_AC365_2021_EOM3-1_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.
o If the table does not display your name, delete the file and download a new copy.

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.
Create a split form for the Coaches table. Save the form, using Coaches Split Form as the name.
2. With the Coaches Split Form still open, navigate to the record for Rusty Horrocks (CoachID 120) and delete the record.
3. Using an update query, update records in the SwimFees table to change the registration fee for LevelID 6 to 175. Save the query using UpdateSwimFees as the query name.
4. Open the Coaches table in Design View and move the Specialty field so that it immediately follows the LastName field. Change the Specialty field to a Lookup field. Enter the following four values: Backstroke, Breaststroke, Butterfly, Freestyle for the Lookup field. Limit the values to only those items on the list and do not allow multiple values for the field. Save the table but do not close it.
5. With the Coaches table still open, switch to Datasheet View and change the Specialty field value for Noelle Gross (who has a CoachID of 104) to Butterfly. Sort the records in the table in ascending order by LastName. Save and close the table.
6. Using the Find Unmatched Query Wizard, find all records in the Members table that do not have matching records in the Swimmers table using the common MemberID field. Display the MemberID, FirstName, LastName, and ClubID from the Members table in the result. Save the query using UnmatchedMembers as the query name.
7. Open the SwimFees table in Design View and change the field size for the LevelDescription field to 50. Add the description Annual fee = monthly fee * 11 if paid in advance to the DiscountedFee field. Add a calculated field named AnnualFee to the end of the table. The field is the result of multiplying MonthlyFee by 12. Save the changes to the table and close the table.
8. The North Carolina and South Carolina swim clubs hold monthly swim competitions against each other. Create a Make Table query that selects all fields from the Members table in the same order that they are listed in the field list. Add criteria to select only those records with NC or SC State field values. Assign the name CarolinaMembers to the table and save the query using NCSCMembers as the query name, then run the query.
9. Open the TotalDiscountedFees query in Design View and use the Expression Builder to create a calculated field the calculates the sum of the RegistrationFee and DiscountedFee field values. Assign the caption TotalFees to the calculated field. Save the query.
10. Create a one-to-many relationship between the Clubs table and the Members table. ClubID is the matching field in each table. Make the relationship enforce referential integrity. Save the relationship.
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 website to submit your completed project.