Shelly Cashman Access 365/2021 | Module 2: End of Module Project 2 | Rural Health Associates (2025)

Rural Health Associates
CREATING AND MODIFYING QUERIES
GETTING STARTED
• Open the file SC_AC365_2021_EOM2-2_FirstLastName_1.accdb, available for
download.
• Save the file SC_AC365_2021_EOM2-2_FirstLastName_1.accdb as
SC_AC365_2021_EOM2-2_FirstLastName_2.accdb
o
o
Edit the file name by changing “1” to “2”.
If you do not see the .accdb file extension, do not type it. The file extension will
be added for you automatically.
• With the file SC_AC365_2021_EOM2-2_FirstLastName_2.accdb open, ensure that
your first and last name is displayed as the first record in the _GradingInfoTable table.
o
If the table does not display your name, delete the file and download a new copy.
PROJECT STEPS
1.
2.
3.
4.
5.
6.
7.
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.
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.
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.
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).
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.
Open the AverageIncome query. Add a total row to the query and calculate the average
income grouped by state. Save the query.
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.
Shelly Cashman Access 365/2021 | Module 2: End of Module Project 2
8.
9.
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.
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 website to submit your completed project.

#MicrosoftOffice
#MicrosoftAccess
#Microsoft
#ShellyCashman
#ShellyCashmanAccess
#SAMProject