New Perspectives Access 2019 | Module 5: End of Module Project 2 Cardiac Rehab Associates Improving Tables and Creating Advanced Queries GETTING STARTED Open the file NP_AC19_EOM5-2_FirstLastName_1

Subject:MS AccessPrice: Bought3

Share With

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

Cardiac Rehab Associates

Improving Tables and Creating Advanced Queries

GETTING STARTED

• Open the file NP_AC19_EOM5-2_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_EOM5-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.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_AC19_EOM5-2_Woman.jpg
• 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 an 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. In this project, you will improve the existing database by modifying field properties in tables and creating different types of queries.
   
   Open the Clients table in Design View. Add three new fields below the InsuranceCode field. The first field should be named ClientEmail with a Hyperlink data type. The second should be named Headshot with an Attachment data type, and the third should be named Notes with a Long Text data type. Modify the Append Only property for the Notes field to Yes, then save the Clients table.
2. While still in Design View of the Clients table, apply the Caption property for the FirstName field to First, and the LastName field to Last. Change the Field Size property for the FirstName and the LastName fields to 30. Apply the Format property for the DateOfBirth and StartDate fields to Short Date. Save the Clients table. If prompted that some data may be lost, click Yes to continue. (Hint: No data is lost because no values exceed 30 characters in either the FirstName or LastName fields.)
3. While still in Design View of the Clients table, change the Required property for the DateOfBirth field to Yes, apply the Validation Rule property for the DateOfBirth field to <Date(), enter Date of birth must be prior to today for the Validation Text property for the DateOfBirth field, and then save the Clients table. If prompted to test data integrity rules, click Yes.
4. While still in Design View of the Clients table, change the Veteran field to Short Text then save the table. Use the Lookup Wizard to create a lookup field for the Veteran field. Type in the values of Army, Navy, Air Force, and Marines. Accept the other default settings in the Lookup Wizard. Save the table if prompted, then modify the List Rows Lookup property for the Veteran field to display 4 rows. Save the Clients table.
5. While still in Design View of the Clients table, add [StartDate]>=[DateOfBirth] as the Validation Rule for the table and enter Start date must be greater than date of birth as the Validation Text property for the table. Save the Clients table. Click Yes if prompted to test for data integrity.
6. Switch to Datasheet View of the Clients table, then use the lookup list to change the Veteran field value from No to Army for the fourth record (ClientID 00003 Leroy Schonthaler).
7. While still in Datasheet View of the Clients table, add the Support_AC19_EOM5-2_Woman.jpg file in the default folder location to the Attachment field for the first record (ClientID 00000 Mary Gochnauer). Add a Total row to the datasheet and average the values in the StrengthScore and FlexibilityScore fields. Save and close the Clients table.
8. Open the InsuranceCount query in Design View. Add Insurance as the Caption property to the InsuranceCode field. Add Clients by Gender and Insurance as the Description property for the query. Add the Total row to the query grid, Count the ClientID field, and sort in ascending order by the InsuranceCode field. Save and view the query in Datasheet View, widen the CountOfClientID field to display the entire field name, then add a Total row to the query datasheet and sum the CountOfClientID field as shown in Figure 1. Save and close the InsuranceCount query.

Figure 1: InsuranceCount Query in Datasheet View

9. Create a new query in Query Design View based on the Clients table. Add the FirstName, LastName, DateOfBirth, and Gender fields in that order. Sort the records in descending order on the DateOfBirth field and return the top 15 records. Add M for male or F for female as a parameter prompt for the Gender field. Save the query with the name Top15ByGender then run the query using F for female as the parameter prompt as shown in Figure 2. Close the Top15ByGender query.

Figure 2: Top15ByGender Query in Datasheet View

10. Create a new query in Query Design View based on the Clients table. Add the Gender, InsuranceCode, and MonthlyFee fields to the query grid in that order. Change the query from a Select query to a Crosstab query. Use InsuranceCode as the Row Heading field, Gender as the Column Heading field, and Sum MonthlyFee as the Value field. Save the query with the name InsuranceGenderCrosstab and view the query in Datasheet View as shown in Figure 3. Close the InsuranceGenderCrosstab query.

Figure 3: InsuranceGenderCrosstab Query in Datasheet View

11. Use the Find Unmatched Query Wizard to find records in the InsuranceCompanies table that contain no related records in the Clients table. The InsuranceCode field in each table contains matching data. Include both the InsuranceCode and CompanyName fields in the query results. Save the query with the name InsuranceCompanies Without Matching Clients and view the results in Datasheet View as shown in Figure 4. Close the InsuranceCompanies Without Matching Clients query.

Figure 4: InsuranceCompanies Without Matching Clients 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.