Homework answers / question archive / Shelly Cashman Access 2019 | Module 2: End of Module Project 2 Rural Health Associates Creating and Modifying Queries GETTING STARTED Open the file SC_AC19_EOM2-2_FirstLastName_1

Shelly Cashman Access 2019 | Module 2: End of Module Project 2 Rural Health Associates Creating and Modifying Queries GETTING STARTED Open the file SC_AC19_EOM2-2_FirstLastName_1

MS Access

Share With

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

Rural Health Associates

Creating and Modifying Queries

GETTING STARTED

• Open the file SC_AC19_EOM2-2_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_EOM2-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.
   Create a query for the Family table that includes the FamilyID, LastName, and State fields for all families. Apply criteria to select only records where the LastName starts with the letter K and then save the query using KFamilies as the query name.
2. Open the BirthDate query and find all patients who have a DateOfBirth after 1/1/1980 and who are female. The DateOfBirth field should display in the query result with the Medium Date format.
3. Create a crosstab query based on the Family table. The crosstab groups the average of income by county and state. Use the County field for the row headings and the State field for the column headings. Calculate the average income. Use Family_Crosstab as the name of the query.
4. Export the ProfessionalStaff table as an Excel file without formatting or layout to the same folder in which your database is stored. Save the export steps, using Export-ProfessionalStaff as the name (note that this is the default name).
5. Create a query based on the Family table that includes the FamilyID, LastName, State, and Income fields for all families with an income greater than $40,000. Save the query using FamilyIncome as the name of the query.
6. Open the AverageIncome query. Add a total row to the query and calculate the average income grouped by state. Save the query.
7. Open the HighIncome query. Add the County field to the query immediately following the State field. Sort the records in descending order by Income. Display only the top 5% of the records in the query result. Save the query.
8. Create a query that joins the Clinics and ProfessionalStaff tables. Include the ClinicName, FirstName, LastName, and Position fields in the query results. Sort the records in ascending order by ClinicName and then by Position. Save the query using Clinics-Staff as the query name.
9. Rename the Doctors query in the Navigation Pane using DoctorInfo as the query name.

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