SAM Project 1a Access Module 01 Databases and Database Objects - An Intro - Personal Insurance, Inc.

Shelly Cashman Access 365/2021 | Module 1: SAM Project 1a

Personal Insurance, Inc.
CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS

GETTING STARTED
• Open the file SC_AC365_2021_1a_FirstLastName_1.accdb, available for download.
• Save the file SC_AC365_2021_1a_FirstLastName_1.accdb as SC_AC365_2021_1a_FirstLastName_2.accdb
o Edit the file name by changing “1” to “2”.
o 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_1a_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. Personal Insurance, Inc. is a national company that insures homeowners and renters. It also offers umbrella policies that provide additional coverage. As a regional manager, you've decided to create an Access database to better manage information on customers and policies.

Create a new table in Datasheet View with the following options:

a. Rename the default primary key ID field PolicyNumber and change the data type to Short Text. PolicyNumber should remain the primary key.
b. Add a new field with the name CustomerID and the Short Text data type.
c. Change the field size of the CustomerID field to 7.
d. Add a new field with the name PersonalProperty and the Currency data type.

Save the table using Renters as the name.
2. With the Renters table still open in Datasheet View, add the record shown in Table 1. Save and close the Renters table.
Table 1: Record for Renters Table

PolicyNumber CustomerID PersonalProperty
R10223 11004 75,000

3. Create a new table in Design View to store the clients of Personal Insurance, Inc. who own homes as follows:
a. Add a field with the name PolicyNumber and the AutoNumber data type.
b. Set PolicyNumber as the table's primary key.
Save the table with the name Homeowners but do not close it.
4. With the Homeowners table still open in Design View, add a second field to the table with the field name CustomerID and the Short Text data type. Save the table and then close it.
5. Open the Customers table in Design View and change the name of the field named First to FirstName and use LastName to change the name of the field named Last.
6. With the Customers table still open in Design View, change the data type property for the CustomerID field from Number to Short Text.
7. In Design View, add a new field to the Customers table after the PostalCode field. The field should be named DateOfBirth and have the Date/Time data type. Save the Customers table.
8. Switch the Customers table to Datasheet View, navigate to the third record (which has a CustomerID field value of 11003), and delete the record.
9. With the Customers table still open in Datasheet View, navigate to the fifth record (which has a CustomerID field value of 11006) and change the value to Alberts for the LastName field. Close the Customers table.
10. You also need to view individual customer records. Use the Form tool to create a form for the Customers table. Save the form with the name Customer Data Form and confirm that the form matches Figure 1. Close the form.
Figure 1: Customer Data Form in Form View

11. Use the Simple Query Wizard to create a query based on the Umbrella table with the following options:

a. Include all fields from the Umbrella table in the query.

Save the query with the name Umbrella Query (which is the default name) and then close the query.
12. You need to refer to a printed copy of the Umbrella table when assigning consultants to projects. Use the Report Wizard to create a report based on the Umbrella table with the following options:
a. Include all fields from the Umbrella table in the report.
b. Do not include any grouping levels.
c. Sort the report by CustomerID in Ascending order.
d. Select Tabular as the layout of the report and Portrait as the orientation of the report.

Save the report using Umbrella Report as the name. Confirm that the report matches Figure 2 and then close the report.
Figure 2: Umbrella Report in Report View

13. In the Navigation Pane, use Umbrella Data Form to rename the Umbrella form.
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.

#microsoftaccess #SAMProject