Homework answers / question archive / Shelly Cashman Access 2019 | Module 3: End of Module Project 2 Rural Health Associates Maintaining a Database GETTING STARTED Open the file SC_AC19_EOM3-2_FirstLastName_1

Shelly Cashman Access 2019 | Module 3: End of Module Project 2 Rural Health Associates Maintaining a Database GETTING STARTED Open the file SC_AC19_EOM3-2_FirstLastName_1

MS Access

Share With

Shelly Cashman Access 2019 | Module 3: End of Module Project 2

Rural Health Associates

Maintaining a Database

GETTING STARTED

• Open the file SC_AC19_EOM3-2_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_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. Rural Health Associates is a healthcare company that manages rural health clinics in underserved areas of the country. You work in the Patient Services Department as an information specialist.
   The records in the AdditionalStaff table need to be appended to the ProfessionalStaff table. Create an append query to append the records. Save the query using AppendStaff as the query name, then run and view it.
2. Open the Family table in Design View and delete the County field. Change the data type for the Income field to Currency with 0 decimal places. Save the changes to the table.
3. Using a query, delete records in the Family table where the Income field value is greater than 55000. Save the query using DeleteHighIncome as the query name, then run and view it.
4. Open the Clinics table in Design View. Create a validation rule for the State field that ensures only the values ID, MT, OR, or WA are entered in the field. Enter Must be ID, MT, OR, or WA as the validation text. Save the changes to the table.
5. Create a one-to-many relationship between the Family table and the Patients table. FamilyID is the matching field in each table. Make the relationship enforce referential integrity. Save the relationship.
6. Open the HealthServices table in Design View and make the ServiceCode field the primary key for the table. Ensure that any letters entered in the ServiceCode field display in uppercase. Save the changes to the table.
7. Using an update query, update the field values in the StateMinInc field in the IncomeLevels table to 16780 for all records where the value in the County field is Twin Falls. Save the query using UpdateStateMinInc as the query name, then run and view it.
8. Open the IncomeLevels table in Design View. Add a calculated field named IncDifference to the end of the table. The field is the result of subtracting StateMinInc from Income. Save the changes to the table.
9. Switch the IncomeLevels table to Datasheet View. Add the caption TaxIncome to the Income field. Add a Total row to the table and calculate the maximum for Income field and the average for the StateMinInc field. Save the changes to the table.

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