Homework answers / question archive / New Perspectives Access 2016 | Module 3: SAM Project 1a Healthy Myles Fitness Center USING QUERIES TO UPDATE AND RETRIEVE INFORMATION   GETTING STARTED Open the file NP_AC16_3a_FirstLastName_1

New Perspectives Access 2016 | Module 3: SAM Project 1a Healthy Myles Fitness Center USING QUERIES TO UPDATE AND RETRIEVE INFORMATION   GETTING STARTED Open the file NP_AC16_3a_FirstLastName_1

MS Access

Share With

---

New Perspectives Access 2016 | Module 3: SAM Project 1a

Healthy Myles Fitness Center

USING QUERIES TO UPDATE AND RETRIEVE INFORMATION

 

GETTING STARTED

• Open the file NP_AC16_3a_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC16_3a_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.

 

PROJECT STEPS

1. You are employed at Healthy Myles Fitness Center and are in charge of consolidating the company’s records and other important information into a database. You have already set up the basic structure of the database and created relationships between tables, and will now work with queries to make information easier to update and retrieve.

Open the tblTrainer table in Datasheet View. Display the subdatasheet for the Randy Lee (TrainerID field value 312), and then update the record with the BillingID field value A80643 to include 7 sessions and an amount of $950. Close the tblTrainer table.

1. Open the tblLocation table, and then delete the Downtown record (LocationID 335). Save and close the tblLocation table. (Hint: if a message warning that you are about to delete 1 record appears, click Yes.)
2. Create a new query in Design View based on the tblClient table. Add the FirstName, LastName, BirthDate, and Gender fields, in that order, to the query. Save the query as qryClientBirthday, run it, and then close it.
3. Create a new query in Design View based on the tblClient, tblBilling, and tblTrainer tables. Save the query as qryClientsAndTrainers, and then do the following:
4. Add the LastName field from the tblClient table to the query.
5. Add the LastName field from the tblTrainer table to the query.
6. Add the StartDate, EndDate, Sessions, and Amount fields, in that order, from the tblBilling table to the query.
7. Save and run the query, and then close it.
   1. Create a new query in Design View that uses the tblTrainer table, and then do the following:
   2. Add the LastName, Specialty, Certification, HireDate, and Minors fields, in that order, to the query design.
   3. Add criteria to the Certification field to select records that contain the value NASM. Save the query as qryTrainerNASM, run it, and then close it.
      1. In the Navigation pane, copy the qryTrainer query and paste it in the navigation pane, rename the copied query as qryTrainer2015, open it in Design view, and then do the following:
      2. Add criteria to the HireDate field to select records with contracts that begin on or after January 1, 2015.
      3. Hide the TrainerID field so it does not appear in the query results but remains in the query design. (Hint: Make sure you hide this field in Design View, not in Datasheet View.)
      4. Save and run the query, and then close it.
         1. In the Navigation pane, copy the qryTrainer query and paste it in the navigation pane, rename the copied query as qryTrainerNSCAMinors, open it in Design view, and then do the following:
         2. Add criteria to the query to select records with the Certification field value NSCA-CPT and the Minors field value Yes.
         3. Sort the records in ascending order by the LastName field.
         4. Move the Minors field so it appears to the right of the Certification field in the query design.
         5. Save and run the query, and then close it.
            1. In the Navigation pane, copy the qryTrainer query and paste it in the navigation pane, rename the copied query as qryTrainerNATAOrMinors, open it in Design view, and then do the following:
            2. Add criteria to the query to select records with the Certification field value NATA or records that indicate that the trainer accepts clients who are minors.
            3. Save and run the query, and then close it.
               1. Open the qryTrainerBilling query in Datasheet View. Change the font size for the datasheet to 12 pt.
               2. With the qryTrainerBilling query still open in Datasheet View, add a total row to the query datasheet. In the total row, use the SUM function to total the values in the Amount field, and use the AVG function to average the values in the Sessions field. Widen the Sessions field to display the result in the total row (approximate width 18.8) Save and close the qryTrainerBilling query.
               3. Create a new query in Design View that is based on the tblBilling and tblClient tables, and then do the following:
               4. Add the ClientID and LastName fields from the tblClient table to the query.
               5. Add the Sessions and Amount fields from the tblBilling table to the query.
               6. Add a total row to the design grid, and group the query results by the LastName field values.
               7. In the total row, use the SUM aggregate function to calculate the sum of the Amount field values. Set the caption of the Amount field to Total Amount. (Hint: Do not type the period.)
               8. In the total row, use the SUM aggregate function to calculate the sum of the Sessions field values. Set the caption of the Sessions field to Number of Sessions. (Hint: Do not type the period.)
               9. Save the query as qryClientTotals. Run the query, and then close it.
                  1. Open the qryClientBilling query in Design View and update it as described below:
                  2. Add a calculated field with the title SessionCost to the end of the query that calculates the cost per session. The formula will calculate the Amount field value divided by the Sessions field value. (Hint: The division symbol is the / symbol.)
                  3. Use Cost per Session for the calculated field’s caption property.
                  4. Format the SessionCost field as currency.
                  5. Move the SessionCost field to the left of the FirstName field.
                  6. Sort the records first in descending order by the SessionCost field values, and then in ascending order by the LastName field values.
                  7. Save and run the query, and then close it.

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.