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

Shelly Cashman Access 2019 Module 2:End of Module Project 1 Competitive Swim Clubs, Inc

MS Access

Share With

Shelly Cashman Access 2019 Module 2:End of Module Project 1

Competitive Swim Clubs, Inc.
CREATE AND MODIFY QUERIES

GETTING STARTED

• Open the file SC_AC19_EOM2-1_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_EOM2-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.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
• Support_AC19_EOM2-1_Swimstrokes.xlsx
• Open the _Grading Info Table 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.

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 query for the Members table that includes the Member ID, First Name, Last Name, and Club ID fields for all members that belong to the club with a ClubID of C02. Save the query using C02Members as the query name.

2. Open the Total Fees query and add a calculated field that sums the Registration Fee field values and the AnnualFee field values. Assign the alias TotalFees to the calculated field. Save the query.
3. Create a parameter query for the Members table that will allow the user to enter a different state each time the query is run. The user should see all fields in the query result. Use Enter desired state as the input prompt. Save the query using Member States as the query name.
4. Create a query that joins the Members table and the Clubs table. Include the ClubName field from the Clubs table and the First Name, Last Name, and State fields from the Members table in the query results. Sort the records in ascending order by Club Name. Save the query using Clubs-Members as the query name.
5. Open the SCMembers query and find all members who belong to the club with a Club ID

of C01 and who reside in South Carolina (SC). Save the query.

6. Open the Members-Swimmers query and change the join properties so that all members are included in the query results. Sort the query in ascending order by LevelID. Save the query.
7. Import the Support_AC19_EOM2-1_SwimStrokes.xlsx file into the Swim Strokes table. Do not save the import steps.
8. Open the Swimmer Levels query and move the LastName field so that it appears after the Swimmer ID field. Find all swimmers who have a LevelID of 5 or 6. Sort the records inascending order by Last Name and then by First Name. Change the caption to Skill Level

for the Level ID field. Save the query.

9. Create a query for the Members table that lists all states in ascending order. Each state should appear only once. Save the query using UniqueStates as the query name.

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.