USA: +1(669)289-8683 
Sign in
New Perspectives Access 2019 | Module 1: End of Module Project 1Homestead Partners
BUILDING AN ACCESS DATABASE
GETTING STARTED
•
Open the file NP_AC19_EOM1-1_FirstLastName_1.accdb, available for download
from the SAM website.
•
Save the file as NP_AC19_EOM1-1_FirstLastName_2.accdb by changing the “1” to a
“2”.
o
•
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.
Homestead Partners is a firm that connects independent home owners with long-term
renters. You work in the Customer Service department of Homestead Partners as a data
analyst. You are developing an Access database application to help manage and analyze
customer satisfaction survey data on the properties rented through your company. In
this project you will improve an existing database by creating and modifying fields and
tables, queries, forms, and reports.
Open the Properties table in Datasheet view and use StreetAddress to rename the
current Address field. Close the Properties table.
2.
Create a new table in Datasheet view. Change the ID field name to ManagerID with the
AutoNumber data type, add a field named ManagerFirstName with the Short Text data
type, add a field named ManagerLastName with the Short Text data type, add a field
named HireDate with the Date/Time data type, and add a field named Salary with the
Currency data type. Save the table with the name Managers and close it.
3.
Open the PropertyTypes table in Datasheet view and then add a new record with Log
Cabin as the Description field value, noting that the ID field automatically increments to
7. Close the PropertyTypes table.
4.
Open the Properties table in Datasheet view and delete the record for PropertyNo 4
(5817 Nall Ave, Sid, Ortiz, Des Moines, IA). Modify the FName value for the record with
PropertyNo 2 (4106 W 67th Ter, Victorina, Hogg, Prairie Valley, KS), using Victor as the
new FName value, and then close the Properties table.
5.
Create a new query using the Simple Query Wizard. Choose the PropertyType, City,
State, and YearBuilt fields from the Properties table. Title the new query with the name
CityListing, open the query to view information, a portion of which is shown in Figure
1, and then close it.
New Perspectives Access 2019 | Module 1: End of Module Project 1
Figure 1: CityListing Query
6.
In the Navigation pane, rename the Property form using PropertyEntry as the new
form name.
7.
Create a new form using the Form Wizard. Choose the FName, LName, City, State, and
Zip fields in the Properties table. Choose a Columnar layout. Title the form OwnerInfo,
open the form to view information as shown in Figure 2, and then close it.
Figure 2: OwnerInfo Form
New Perspectives Access 2019 | Module 1: End of Module Project 1
8.
Create a new report using the Report Wizard. Choose the PropertyNo, City, State, and
YearBuilt fields from the Properties table. Do not add any grouping levels. Sort the
records in ascending order by State and then in ascending order by City. Use a Tabular
layout and a Portrait orientation. Title the report PropertiesByState, preview the
report, a portion of which is shown in Figure 3, and then close it.
Figure 3: PropertiesByState Report
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.
New Perspectives Access 2019 | Module 1: SAM Project 1a
Global Human Resources Consultants
CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS
GETTING STARTED
•
Open the file NP_AC19_1a_FirstLastName_1.accdb, available for download from the
SAM website.
•
Save the file as NP_AC19_1a_FirstLastName_2.accdb by changing the “1” to a “2”.
o
•
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.
You are a project manager for Global Human Resources Consultants, an organization
that provides human resources services to emerging companies located in eastern
Europe. To better manage consultants and the projects on which they work, you have
decided to create an Access database.
Create a new table in Datasheet View with the following options:
a.
Rename the default primary key ID field CountryCode and change the data type
to Short Text.
b.
Change the field size of the field to 3.
c.
Add a new field with the name CountryName and the Short Text data type.
Save the table, using Country as the new name.
2.
With the Country table still open in Datasheet View, add the four records shown in Table
1. Save and close the Country table.
Table 1: New Records for Country Table
CountryCode
BEL
BGR
ROU
SRB
3.
CountryName
Belarus
Bulgaria
Romania
Serbia
Create a new table in Design View to store the clients of Global as follows:
a.
Add a field with the name ClientID and the AutoNumber data type.
b.
Apply ClientID as the table’s primary key.
Save the table with the name Client but do not close it.
New Perspectives Access 2019 | Module 1: SAM Project 1a
4.
With the Client table still open in Design View, add a second field to the table with the
field name ClientName and the Short Text data type. Save the table and then close it.
5.
Open the Consultant table in Design view and update the Last field, using LastName as
the new value.
6.
With the Consultant table still open in Design View, change the data type property for
the ConsultantID field from Number to Short Text.
7.
In Design View, add a new field to the Consultant table after the LastName field. The
field should be named StartDate and have the Date/Time data type. Save and close
the Consultant table.
8.
Open the Consultant table in Datasheet View, navigate to the seventh record (which has
a ConsultantID field value of 110460), and delete the record.
9.
With the Consultant table still open in Datasheet View, navigate to the fourth record
(which has a ConsultantID field value of 110351) and update the FirstName record,
using Sidney as the new value. Close the Consultant table.
10.
You also need to view individual consultant records. Use the Form Wizard to create a
form for the Consultant table with the following options:
a.
Include all fields from the Consultant table in the form.
b.
Select Columnar as the layout for the form.
Save the form with the name Consultant Data Form and confirm that the form
matches Figure 1. Close the form.
Figure 1: Consultant Data Form in Form View
11.
Use the Simple Query Wizard to create a query based on the Skill table with the
following options:
a.
Include all fields from the Skill table in the query.
New Perspectives Access 2019 | Module 1: SAM Project 1a
Save the query with the name Skill Query (which is the default name) and then
close the query.
12.
You need to refer to a printed copy of the Skill table when assigning consultants to
projects. Use the Report Wizard to create a report based on the Skill table with the
following options:
a.
Include all fields from the Skill table in the report.
b.
Do not include any grouping levels.
c.
Sort the report by SkillCode in ascending order.
d.
Select Tabular as the layout of the report and Portrait as the orientation of the
report.
Save the report with the name Skill Report. Confirm that the report matches
Figure 2 and then close the report.
Figure 2: Skill Report in Print Preview
13.
In the Navigation Pane, rename the Skill form, using Skill Data Form as the new value.
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.
New Perspectives Access 2019 | Module 1: SAM Project 1a
Global Human Resources Consultants
CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS
GETTING STARTED
•
Open the file NP_AC19_1a_FirstLastName_1.accdb, available for download from the
SAM website.
•
Save the file as NP_AC19_1a_FirstLastName_2.accdb by changing the “1” to a “2”.
o
•
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.
You are a project manager for Global Human Resources Consultants, an organization
that provides human resources services to emerging companies located in eastern
Europe. To better manage consultants and the projects on which they work, you have
decided to create an Access database.
Create a new table in Datasheet View with the following options:
a.
Rename the default primary key ID field CountryCode and change the data type
to Short Text.
b.
Change the field size of the field to 3.
c.
Add a new field with the name CountryName and the Short Text data type.
Save the table, using Country as the new name.
2.
With the Country table still open in Datasheet View, add the four records shown in Table
1. Save and close the Country table.
Table 1: New Records for Country Table
CountryCode
BEL
BGR
ROU
SRB
3.
CountryName
Belarus
Bulgaria
Romania
Serbia
Create a new table in Design View to store the clients of Global as follows:
a.
Add a field with the name ClientID and the AutoNumber data type.
b.
Apply ClientID as the table’s primary key.
Save the table with the name Client but do not close it.
New Perspectives Access 2019 | Module 1: SAM Project 1a
4.
With the Client table still open in Design View, add a second field to the table with the
field name ClientName and the Short Text data type. Save the table and then close it.
5.
Open the Consultant table in Design view and update the Last field, using LastName as
the new value.
6.
With the Consultant table still open in Design View, change the data type property for
the ConsultantID field from Number to Short Text.
7.
In Design View, add a new field to the Consultant table after the LastName field. The
field should be named StartDate and have the Date/Time data type. Save and close
the Consultant table.
8.
Open the Consultant table in Datasheet View, navigate to the seventh record (which has
a ConsultantID field value of 110460), and delete the record.
9.
With the Consultant table still open in Datasheet View, navigate to the fourth record
(which has a ConsultantID field value of 110351) and update the FirstName record,
using Sidney as the new value. Close the Consultant table.
10.
You also need to view individual consultant records. Use the Form Wizard to create a
form for the Consultant table with the following options:
a.
Include all fields from the Consultant table in the form.
b.
Select Columnar as the layout for the form.
Save the form with the name Consultant Data Form and confirm that the form
matches Figure 1. Close the form.
Figure 1: Consultant Data Form in Form View
11.
Use the Simple Query Wizard to create a query based on the Skill table with the
following options:
a.
Include all fields from the Skill table in the query.
New Perspectives Access 2019 | Module 1: SAM Project 1a
Save the query with the name Skill Query (which is the default name) and then
close the query.
12.
You need to refer to a printed copy of the Skill table when assigning consultants to
projects. Use the Report Wizard to create a report based on the Skill table with the
following options:
a.
Include all fields from the Skill table in the report.
b.
Do not include any grouping levels.
c.
Sort the report by SkillCode in ascending order.
d.
Select Tabular as the layout of the report and Portrait as the orientation of the
report.
Save the report with the name Skill Report. Confirm that the report matches
Figure 2 and then close the report.
Figure 2: Skill Report in Print Preview
13.
In the Navigation Pane, rename the Skill form, using Skill Data Form as the new value.
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.
New Perspectives Access 2019 | Module 1: End of Module Project 1
Homestead Partners
BUILDING AN ACCESS DATABASE
GETTING STARTED
•
Open the file NP_AC19_EOM1-1_FirstLastName_1.accdb, available for download
from the SAM website.
•
Save the file as NP_AC19_EOM1-1_FirstLastName_2.accdb by changing the “1” to a
“2”.
o
•
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.
Homestead Partners is a firm that connects independent home owners with long-term
renters. You work in the Customer Service department of Homestead Partners as a data
analyst. You are developing an Access database application to help manage and analyze
customer satisfaction survey data on the properties rented through your company. In
this project you will improve an existing database by creating and modifying fields and
tables, queries, forms, and reports.
Open the Properties table in Datasheet view and use StreetAddress to rename the
current Address field. Close the Properties table.
2.
Create a new table in Datasheet view. Change the ID field name to ManagerID with the
AutoNumber data type, add a field named ManagerFirstName with the Short Text data
type, add a field named ManagerLastName with the Short Text data type, add a field
named HireDate with the Date/Time data type, and add a field named Salary with the
Currency data type. Save the table with the name Managers and close it.
3.
Open the PropertyTypes table in Datasheet view and then add a new record with Log
Cabin as the Description field value, noting that the ID field automatically increments to
7. Close the PropertyTypes table.
4.
Open the Properties table in Datasheet view and delete the record for PropertyNo 4
(5817 Nall Ave, Sid, Ortiz, Des Moines, IA). Modify the FName value for the record with
PropertyNo 2 (4106 W 67th Ter, Victorina, Hogg, Prairie Valley, KS), using Victor as the
new FName value, and then close the Properties table.
5.
Create a new query using the Simple Query Wizard. Choose the PropertyType, City,
State, and YearBuilt fields from the Properties table. Title the new query with the name
CityListing, open the query to view information, a portion of which is shown in Figure
1, and then close it.
New Perspectives Access 2019 | Module 1: End of Module Project 1
Figure 1: CityListing Query
6.
In the Navigation pane, rename the Property form using PropertyEntry as the new
form name.
7.
Create a new form using the Form Wizard. Choose the FName, LName, City, State, and
Zip fields in the Properties table. Choose a Columnar layout. Title the form OwnerInfo,
open the form to view information as shown in Figure 2, and then close it.
Figure 2: OwnerInfo Form
New Perspectives Access 2019 | Module 1: End of Module Project 1
8.
Create a new report using the Report Wizard. Choose the PropertyNo, City, State, and
YearBuilt fields from the Properties table. Do not add any grouping levels. Sort the
records in ascending order by State and then in ascending order by City. Use a Tabular
layout and a Portrait orientation. Title the report PropertiesByState, preview the
report, a portion of which is shown in Figure 3, and then close it.
Figure 3: PropertiesByState Report
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.
