Homework answers / question archive / New Perspectives Access 2016 | Module 3: SAM Project 1b GRMH Recreation Centers USING QUERIES TO UPDATE AND RETRIEVE INFORMATION   GETTING STARTED Open the file NP_AC16_3b_FirstLastName_1

New Perspectives Access 2016 | Module 3: SAM Project 1b GRMH Recreation Centers USING QUERIES TO UPDATE AND RETRIEVE INFORMATION   GETTING STARTED Open the file NP_AC16_3b_FirstLastName_1

MS Access

Share With

---

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

GRMH Recreation Centers

USING QUERIES TO UPDATE AND RETRIEVE INFORMATION

 

GETTING STARTED

• Open the file NP_AC16_3b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC16_3b_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 an employee at the GRMH Recreation Centers in Grand Rapids, MI. Your supervisor, Samantha Cooper, has decided to create an Access database to more easily track information such as trainer, member, and billing data, and has asked for your help to create it. In this project, you’ll work with queries that will help you find and update information more easily.

Open the tblTrainer table in Datasheet View. Display the subdatasheet for the record with the 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 record with the LocationName field value Downtown. Save and close the tblLocation table.
2. Create a new query in Design View based on the tblMember table. Add the FirstName, LastName, BirthDate, and Gender fields, in that order, to the query design. Save the query as qryMemberBirthday, run it, and then close it.
3. Create a new query in Design View based on the tblMember, tblBilling, and tblTrainer tables. Save the query as qryMembersAndTrainers, and then do the following:
4. Add the LastName field from the tblMember 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 RYT. Save the query as qryTrainerRYT, run it, and then close it.
      1. In the Navigation pane, copy the qryTrainer query, rename the copied query as qryTrainer2016, and then do the following:
      2. Add criteria to the HireDate field to select records with contracts that begin on or after January 1, 2016.
      3. Hide the TrainerID field in Design View. (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, rename the copied query as qryTrainerNSCAMinors, and then do the following:
         2. Add new 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 that 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, rename the copied query as qryTrainerRYTOrMinors, and then do the following:
            2. Add new criteria to the query to select a record with the Certification field value RYT or a record that indicates 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 Maximum function to view the highest value in the Sessions field. Close the qryTrainerBilling query, saving it if necessary.
               3. Create a new query in Design View based on the tblBilling and tblMember tables, and then do the following:
               4. Add the MemberID and LastName fields from the tblMember 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 MemberID and LastName field values.
               7. In the total row, use the SUM aggregate function to calculate the sum of the Amount field values. Then, 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 Sessionsfield to Number of Sessions. (Hint:Do not type the period.)
               9. Save the query as qryMemberTotals, run the query, and then close it.
                  1. Open the qryMemberBilling query in Design View, and then 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. Build the formula that calculates 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.