New Perspectives Access 2019 | Module 3: End of Module Project 2 Cardiac Rehab Associates Creating and modifying Queries GETTING STARTED Open the file NP_AC19_EOM3-2_FirstLastName_1

Subject:MS AccessPrice:19.99 Bought3

Share With

New Perspectives Access 2019 | Module 3: End of Module Project 2

Cardiac Rehab Associates

Creating and modifying Queries

GETTING STARTED

• Open the file NP_AC19_EOM3-2_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_EOM3-2_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. Cardiac Rehab Associates is a company that helps people rehabilitate their health and strength after an illness or accident. You work as a research analyst to maintain records and to analyze the success of various activities and programs. At Cardiac Rehab Associates you are developing an Access database application to help manage this information.
   
   Open the Clients table in Datasheet view and in the record with the ClientID value of 00005 (Tom Thelen), insert Sammy for the FirstName value and Frazer for the LastName value. Hide the CognitiveScore column that does not yet contain any data. Modify the expression in the calculated MonthlyFee field to return 12 instead of 10 for the false part of the condition. Sort the records in ascending order by the LastName field. Save and close the Clients table.
2. Open the ClientListing query in Datasheet view and for ClientID 00048, change the InsuranceCode value to CIG. Note that the CompanyName value will automatically update to Cigna due to the relationships between the Clients and InsuranceCompanies tables. Switch the query to Design view, then sort the records in ascending order by ClientID.
3. With the ClientListing query still open in Design view, use a wildcard character to select all records where the LastName starts with the letter C. Save and display the ClientListing query in Datasheet view as shown in Figure 1 and then close it.

Figure 1: ClientListing Query in Datasheet View

4. Create a new query in Query Design view using the Enrollments and HealthPrograms tables. Join the Enrollments and HealthPrograms tables using the ProgramID field from the Enrollments table and the ID field from the HealthPrograms table. Add the EnrollmentDate and ClientID fields from the Enrollments table and the ProgramName and Fee fields from the HealthPrograms table to the query grid, and in that order. Add an ascending sort order to the EnrollmentDate field. Save the query with the name EnrollmentListing, display it in Datasheet view as shown in Figure 2, and then close it.

Figure 2: EnrollmentListing Query in Datasheet View

5. Open the ClientInsurance query in Design view and add criteria to select all records that have a DateOfBirth greater than or equal to 1/1/1980. Add an ascending sort order to the LastName and FirstName fields. Delete the Gender field from the query grid, and move the InsuranceCode field to be the third field. Save and display the ClientInsurance query in Datasheet view as shown in Figure 3 and then close it.

Figure 3: ClientInsurance Query in Datasheet View

6. Open the ClientScores query in Design view and add criteria to select all records that have a Gender field value of M, a StrengthScore field value of less than 5, and a FlexibilityScore value of less than 5. Save and display the ClientScores query in Datasheet view as shown in Figure 4 and then close it.

Figure 4: ClientScores Query in Datasheet View

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.