SAM Project - Carolinas Health Club CREATING AND MODIFYING QUERIES

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: SC_Access2013_C2_P1b_FirstLastName_1.accdb

Open the file you just downloaded and save it with the name: SC_Access2013_C2_P1b_FirstLastName_2.accdb

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 Years Experience 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.

Attachment:- Assignment File.rar