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

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

Personal Insurance, Inc.

Creating Queries

GETTING STARTED

Open the file
SC_AC365_2021_2a_
FirstLastName_1.accdb
, available for download.

Save the file
SC_AC365_2021_2a_
FirstLastName_1.accdb
as
SC_AC365_2021_2a_
FirstLastName_2.accdb

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.

To complete this Project, you will also need the following files:

Support_AC365_2021_2a_UmbrellaData.xlsx

With the file
SC_AC365_2021_2a_
FirstLastName_2.accdb
open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.

If the table does not display your name, delete the file and download a new copy.

PROJECT STEPS

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 need to be able to query the database to help make decisions and to answer questions from other employees.

Create a query based on the
Customers table in Query Design View with the following options:

Add the
CustomerID,
FirstName,
LastName, and
DateOfBirth fields to the design grid in that order.

Sort the records in
ascending order by
LastName.

Save the query using
CustomerLastNameSorted as the name.
Open the query in Datasheet View, then close it, saving if necessary.

Open the
HomeownersPremium query in Design View and make the following changes to the query:

Delete the
CustomerID column from the design grid.

Add criteria to select only those records where the
Premium field value is
greater than
2,000.

Save the changes to the
HomeownersPremium query.
Open the query in Datasheet View, then close it, saving if necessary.

Open the
LiabilityOrProperty query in Design View and add criteria to select only those records where the
Liability field values
equal
75,000
or the
PersonalProperty field values
equal
75,000. Save the changes to the query. Open the query in Datasheet View, confirm that 3 records appear in the
LiabilityOrProperty query results, then close the query, saving if necessary.

Open the
ComparisonBirth query in Design View and make the following changes to the query:

Add the
FirstName field to the query design grid. The
FirstName field should immediately follow the
CustomerID field.

Add criteria to select only those records where the
DateOfBirth field value is
less than 1/1/1950.

Set the format to
Medium Date for the
DateOfBirth field.

Set the caption to
BirthDate for the
DateOfBirth field.

Save the changes to the
ComparisonBirth query.
Open the query in Datasheet View, confirm that the results match Figure 1, then close it, saving if necessary.

Figure 1: ComparisonBirth query results

Open the
StateCitySort query in Design View and make the following changes:

Move the
State field to the
beginning of the design grid so that the order of the fields in the grid is
State,
City,
FirstName, and
LastName.

Sort the records in
descending order by the
State field and in
ascending order by
City.
Save the changes to the query. View the query in Datasheet View, then close it, saving if necessary.

Open the
LiabilityAndProperty query in Design View and add criteria to select only those records where the
Liability field values
equal
100,000
and the
PersonalProperty field values
equal
100,000. Save the changes to the query. Open the
LiabilityAndProperty query in Datasheet View, confirm that 2 records appear in the query results, then close the query, saving if necessary.

Open the
MichiganCustomers query in Design View and add criteria to select only those records where the
State field value is
MI. Save the changes to the query. Open the query in Datasheet View, confirm that 7 records appear in the query results, then close the query, saving if necessary.

Because customers of Personal Insurance reside in many different cities, it is often difficult to know the exact spelling of a city. Open the
CustomerCity query in Design View and add criteria to select only those records where the
City field value
begins with the letters Al. Save the changes to the query. Open the query in Datasheet View, confirm that 2 records appear in the query results, then close the query, saving if necessary.

Many queries require data from more than one table. For example, you may want a query to display the customer last name rather than the customer ID for a renter’s policy. Create a query in Design View based on the
Customers and
Renters tables with the following options:

Add the
Customers table and the
Renters table to the design window.

Add the
LastName field from the
Customers table to the design grid.

Add the
PolicyNumber and
Premium field from the
Renters table to the design grid.

Join the
Customers table and the
Renters table by drawing a line from the
CustomerID field in the
Customers table to the
CustomerID field in the
Renters table. (Hint: Because the field names are identical in both tables, the line may already be there. Do not attempt to recreate the line if it is already present.)

Save the query using
Customer-Renter as the name.
Open the query in Datasheet View, then close it, saving if necessary.

Because customers live in different states, it is often advantageous to create a query where you can change one criteria using a parameter query. Open the
StatesParameter query in Design View.

Add parameter criteria to the
State field to replace the current “FL” criteria. The new parameter criteria should prompt the user with
Enter desired state as the text.

Save the changes to the query.

View the query in Datasheet View. Enter
PA when prompted.
Confirm that 8 records appear in the query results then close the query, saving if necessary.

Open the
TopValuesProperty query in Design View. Modify the query to
sort the
PropertyDamage amounts in
descending order. Change the Return value to display only the top
5 records. Open the query in Datasheet view, then close the query, saving if necessary.

Open the
TotalCoverage query in Design View. Modify the query by creating a calculated field. Enter
TotalCoverage: [Liability]+[PropertyDamage] in the Zoom dialog box of the first empty column in the design grid. Save the query. Open the query in Datasheet View, then close the query, saving if necessary.

Open the
AveragePremium query in Design View and perform the following tasks:

Add a
Totals row to the design grid.

Select
Avg as the calculation in the Total row.

Set the caption to
Avg Prem for the Premium field.

Save the changes to the query. Open the query in Datasheet View and confirm that it matches Figure 2. Close the query, saving if necessary.

Figure 2: AveragePremium query results

Open the
CustomersWithoutHomes query in Design View and perform the following tasks:

Change the
join property for the relationship between the
Customers and
Homeowners tables to select
ALL records from the
Customers table and only those records from the
Homeowners table where the joined fields are equal.

Add the
Is Null criteria for the
PolicyNumber field and add an
Ascending sort order on the
LastName field.
Save the changes to the query. Open the query in Datasheet View and confirm that there are 21 records in the query result. Close the query, saving if necessary.

Open the
UniqueStates query in Design View. Modify the query to list all states only once. Save the changes to the query. Open the query in Datasheet View and confirm that there are 15 records in the query result. Close the query, saving if necessary.

Create a crosstab query based on the
MidAtlantic table with the following options:

Use only data from the
MidAtlantic table in the crosstab.

Use the
State field for the row headings.

Use the
City field for the column headings.

Use a
Count of the
CustomerID field as the calculated value for each row and column intersection, and include row sums in the crosstab query.

Save the query using
State-City Crosstab as the name.
View the query, then save and close it.

Export the
Renters table as an Excel file (.xlsx) with the name
Renters to the same folder as the one that stores your database. Do not export the data with formatting and layout. Save the export steps using
Export-Renters as the name. Do not add a description.
Save the changes to the table and close it.

Use the Import Spreadsheet Wizard to import the data from the
Support_AC365_2021_2a_UmbrellaData.xlsx support file and
append it to the
Umbrella table. Do not save the Import steps. Open the
Umbrella table in Datasheet View. It should contain 14 records. Close the table.

Rename the
PennsylvaniaOwners query as
PennsylvaniaCustomers in the Navigation Pane.

Group the objects in the Navigation Pane by
Tables and Related Views.

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.

855ead4b-494c-4009-b0a1-165337e8d556.jpeg

5a77ba16-6915-46f6-8596-3dcbe1a2c412.jpeg

image9.png

image6.png

image7.png

image8.png