PROJECT DESCRIPTION

Carolinas Health Club is a health and fitness center located near Charlotte, North Carolina.
The club provides a variety of fitness classes, such as cycling, aerobics, pilates, and yoga for
active adults. The club also provides personal training for its members. With a recent growth
in business, Carolinas Health Club has developed a database in Access 2013 to organize its
records. The health club would like your help modifying some of the queries in the database
and would also like you to create some new queries that will help increase the club’s
efficiency.

GETTING STARTED

 Download the following file from the SAM website:
o SC_Access2013_C2_P1b_FirstLastName_1.accdb
 Open the file you just downloaded and save it with the name:
o SC_Access2013_C2_P1b_FirstLastName_2.accdb
o Hint: If you do not see the .accdb file extension in the Save file dialog box, do
not type it. Access 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.

PROJECT STEPS

1. Open the Trainer Contact Query in Design view. Delete the HourlyRate field
   from the query, then save, run, and close the query.
2. Create a new query in Design view based on the Trainers table with the
   following options:
   a. Add the fields TrainerID, FirstName, LastName, and
   HourlyRate to the query in that order.
   b. Add an ascending sort order on the HourlyRate field.
   c. Save the query with the name Trainer Rate Query.
   Run the Trainer Rate Query and then close it.
3. Create a new parameter query in Design view based on the Trainers table with
   the following options:

a. Add the FirstName, LastName, Phone, Street, City, State, and
PostalCode fields (in that order) from the Trainers table to the
query.
b. Add the criterion [Enter City] (including brackets) to the City field.
c. Save the query with the name City Trainer Query.

Run the City Trainer Query to confirm it works (Note: If you use Charlotte for
the parameter value, the query should return 2 records.) Save and close your
query.

4. Create a new query in Design view based on the Classes and Sessions tables
   with the following options:
   a. Add the Description field from the Classes table to the query.
   b. Add the Day and Time fields (in that order) from the Sessions table
   to the query.
   c. Save the query with the name Class Sessions Query.
   Run the Class Sessions Query, then save and close it.
5. Create a Crosstab query based on the Sessions table with the following
   options:
   a. Use only data from the Sessions table in the query.
   b. Use the ClassID field for the row headings.
   c. Use the TrainerID field for the column headings.
   d. Use a Count of the SessionID field as the calculated value for
   each row and column intersection and include row sums in the
   crosstab query.
   e. Save the query with the name Sessions-Trainer Crosstab.
   View the query, then save and close it.
6. Open the 60 Minute Classes Query in Design view. Modify the query to show
   only records where the Length field is equal to 60. Run the 60 Minute Classes
   query, save and then close it.
7. Open the Pineville NC Query in Design view and add criteria to select only
   those records where the City field is equal to Pineville and the State field is
   equal to NC. Run the query, then save and close it.
8. Open the Trainer Experience Query in Design view, hide the HourlyRate field
   in the query, then save and close it.
9. Open the SC Cities Query in Design view and add a criteria to select only
   those records where the City field is equal to Fort Mill or to Rock Hill. Run
   the query, then save and close it.
10. Open the Class Costs Query in Design view. Modify the query to sort the
    records in ascending order by the Description field and the Fee field. Run the
    Class Costs Query, save, then close it.

11. Open the Limited Experience Query in Design view and add criteria to select
    only those records where the value in the YearsExperience field is less than 2.
    Run the query, then save and close it.
12. Open the Sessions by Class Query in Design view and modify it by adding
    totals to the query. For the ClassID field, set the total row to Group By. For
    the SessionID field, set the total row to Count. Run the query, then save and
    close the query.
13. Open the E Members Query in Design View and add the E* wildcard criteria to
    the LastName field, so that the query only returns records where with
    member’s last name that begin with the letter E. Run the query, then save
    and close the query.
14. Use the form button to create a simple form based on the Trainer Experience
    Query. Save the form as Trainer Experience Form and close the form.
15. Create a new report using the Report Wizard based on the All Sessions Query
    with the following options:
    a. Include all fields from the All Sessions Query in the report.
    b. The report will automatically be grouped by the TrainerID field, but
    use no additional grouping in the report.
    c. Use no additional sorting in the report.
    d. Use a Stepped layout and Portrait orientation for the report.
    e. Set the title of the report to All Sessions Report.
    Preview the report, then save and close it.