Exp19_Access_Ch06_Cap – Insurance 1.0

Papa_

EX19_AC_CH06_GRADER_CAP

_AS.zip

Exp19_Access_Ch06_Cap – Insurance 1.0

Exp19 Access Ch06 Cap Insurance 1.0 

Access Chapter 6 Capstone – Insurance  

EX19_AC_CH06_GRADER_CAP
  

Project Description:

You have been asked to modify a database that tracks driver data and insurance details. You will update, add to, and delete records from tables. You will also create queries that aggregate data, find unmatched, and find duplicate values.

Start   Access. Open the downloaded Access file named Exp19_Access_Ch6_Cap_Insurance. Grader has automatically added   your last name to the beginning of the filename. Save the file to the   location where you are storing your files.

Open   the Drivers table, observe the data, and then close the table. Create an   update query based on the Drivers table. Include all of the fields from the   table.

Set   the criteria to update Special   drivers to Senior.   Run the query, save it as Update Class,   and then close the query.

Make   a copy of the Make Non-Standard   Drivers query and save it as Append Non-Standard   Drivers.

Change   the query type to Append and   append records to Non-Standard Drivers.   Set the criteria to select Minor   drivers. Run, save, and then close the query.

Set   DriverID as the primary key field   of the Non-Standard Drivers table. Save and close the table.

Make   a copy of the Append Non-Standard   Drivers query and save it as Delete Non-Standard   Drivers.

Change   the query type to Delete. Set the   criteria to select Minor   or Senior drivers. Run, save, and then close the   query.

Create   a crosstab query based on the Non-Standard Drivers table. Set Class as the row heading field and Gender as the column heading field.   Summarize the data by counting the DriverIDs. Save the query using the   default name, and view the query results.

Modify   the query so that Class displays   as the column heading and Gender   displays as the row heading. Save the query as Non-Standard   Drivers_Crosstab and run the query.

Modify   the database (Non-Standard Drivers table) so that the gender M displays as Male and F displays as Female.   Run the crosstab query again to display the changes. Close the query.

Create   a find duplicates query based on the Drivers table where there is a repeated   LastName and Street value. Add the DriverID   and FirstName fields to the   query results. Save the query using the default name, and view the query   results. Note that Eric and Kirk Abelson live at the same address, but have   two different DriverIDs. In the Drivers table, expand the Subdatasheet for   Eric and notice that he carries insurance for both household vehicles. Close   the table and the query.

Create   an unmatched query to find drivers in the Drivers table who have no insurance   listed in the Insurance table. Include all fields from the Drivers table.   Save the query using the default name, and view the query results.

For   the driver named Lawrence Alexander, add a record to the Insurance table.   Enter the InsuranceID number as 10010,   the DriverID as 341890123,   and the Agent as AS8842.   Populate the remaining fields (AutoType, AutoYear, TagID, and TagExpiration)   with data of your choice. Close the table. Run the Drivers Without Matching Insurance query again. Note that   Lawrence Alexander no longer appears in the results, as you have used the query   to identify missing data and corrected the error. Kirk Abelson is covered   under the same policy as Eric, his father. Close the query.

Close   all database objects. Close the database and then exit Access. Submit the   database as directed.

EX19_AC_CH06_GRADER_CAP_AS_Instructions x
Grader – Instructions Access 2019 Project

Exp19_Access_Ch06_Cap – Insurance 1.0

Project Description:

You have been asked to modify a database that tracks driver data and insurance details. You will update, add to, and delete records from tables. You will also create queries that aggregate data, find unmatched, and find duplicate values.

Steps to Perform:

Step

Instructions

Points Possible

1

Start Access. Open the downloaded Access file named
Exp19_Access_Ch6_Cap_Insurance. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.

0

2

Open the
Drivers
table, observe the data, and then close the table. Create an update query based on the Drivers table. Include all of the fields from the table.

8

3

Set the criteria to update
Special drivers to
Senior. Run the query, save it as
Update Class, and then close the query.

6

4

Create a make table query based on the Drivers table. Include all of the fields from the table. Set the criteria to select
Senior drivers. The query should make a new table named
Non-Standard Drivers in the current database.

10

5

Run the query, save it as
Make Non-Standard Drivers and then close the query.

4

6

Make a copy of the
Make Non-Standard Drivers query and save it as
Append Non-Standard Drivers.

4

7

Change the query type to
Append and append records to
Non-Standard Drivers. Set the criteria to select
Minor drivers. Run, save, and then close the query.

6

8

Set
DriverID as the primary key field of the Non-Standard Drivers table. Save and close the table.

4

9

Make a copy of the
Append Non-Standard Drivers query and save it as
Delete Non-Standard Drivers.

4

10

Change the query type to
Delete. Set the criteria to select
Minor or
Senior drivers. Run, save, and then close the query.

6

11

Create a crosstab query based on the Non-Standard Drivers table. Set
Class as the row heading field and
Gender as the column heading field. Summarize the data by counting the DriverIDs. Save the query using the default name, and view the query results.

8

12

Modify the query so that
Class displays as the column heading and
Gender displays as the row heading. Save the query as
Non-Standard Drivers_Crosstab and run the query.

6

13

Modify the database (Non-Standard Drivers table) so that the gender
M displays as
Male and
F displays as
Female. Run the crosstab query again to display the changes. Close the query.

6

14

Create a find duplicates query based on the Drivers table where there is a repeated LastName and Street value. Add the
DriverID and
FirstName fields to the query results. Save the query using the default name, and view the query results. Note that Eric and Kirk Abelson live at the same address, but have two different DriverIDs. In the Drivers table, expand the Subdatasheet for Eric and notice that he carries insurance for both household vehicles. Close the table and the query.

11

15

Create an unmatched query to find drivers in the Drivers table who have no insurance listed in the Insurance table. Include all fields from the Drivers table. Save the query using the default name, and view the query results.

11

16

For the driver named Lawrence Alexander, add a record to the Insurance table. Enter the InsuranceID number as
10010, the DriverID as
341890123, and the Agent as
AS8842. Populate the remaining fields (AutoType, AutoYear, TagID, and TagExpiration) with data of your choice. Close the table. Run the
Drivers Without Matching Insurance query again. Note that Lawrence Alexander no longer appears in the results, as you have used the query to identify missing data and corrected the error. Kirk Abelson is covered under the same policy as Eric, his father. Close the query.

6

17

Close all database objects. Close the database and then exit Access. Submit the database as directed.

0

Total Points

100

Created On: 07/11/2019 1 Exp19_Access_Ch06_Cap – Insurance 1.0

Papa_Exp19_Access_Ch6_Cap_Insurance.accdb

AgentID
InsuranceCo
InsAgentFirstName
InsAgentLastName
InsAddress
InsCity
InsState
InsZIP
InsPhone
mSysRowId

AS2234
AS
William
Smith

7035556657
CcWt6kv3VFAhS4Q7QYMJG/ZxVcywiuNwksT0jmb5qUU=-~+JQVZooD5HQ10gcYSAfCuw==

AS8842
AS
Anna
Taylor

7035559998
CcWt6kv3VFAhS4Q7QYMJG/ZxVcywiuNwksT0jmb5qUU=-~+JQVZooD5HQ10gcYSAfCuw==

SF4485
SF
Terri
Jones

7035558876
CcWt6kv3VFAhS4Q7QYMJG/ZxVcywiuNwksT0jmb5qUU=-~+JQVZooD5HQ10gcYSAfCuw==

SF8567
SF
James
Johnson

7035551243
CcWt6kv3VFAhS4Q7QYMJG/ZxVcywiuNwksT0jmb5qUU=-~+JQVZooD5HQ10gcYSAfCuw==

SF8870
SF
Darin
Miller

7035550987
CcWt6kv3VFAhS4Q7QYMJG/ZxVcywiuNwksT0jmb5qUU=-~+JQVZooD5HQ10gcYSAfCuw==

DriverID
StateIssued
Photo
Class
FirstName
MiddleName
LastName
Street
Apartment
City
State
ZIP
HomePhone
Gender
Height
Weight
HairColor
EyeColor
OrganDonor
Restrictions
IssueDate
ExpirationDate
DateOfBirth

123456789
LA

Adult
Pamela
R
Toliver
7015 Westchester Drive

Baton Rouge
LA
70810
2255552752
F
5′ 7″
135
Blonde
Brown
true
2
2008-07-05

1985-11-22

341012345
LA

Adult
Brian
Andrew
Andrews
132 Blue Street

Baton Rouge
LA
70810
2255553385
M
5′ 10″
200.00
Brown
Brown
true
4
1995-05-05

1975-05-15

341098765
LA

Adult
Victoria
May
Bowman
20 Spruce Avenue

Baton Rouge
LA
70810
2255559876
F
5′ 5″
130.00
Red
Hazel
false
3
1999-06-10

1969-06-20

341123456
LA

Minor
James
Hallard
Barnes
8 Shane Drive

Baton Rouge
LA
70810
2255556712
M
6′ 1″
205.00
Blonde
Brown
false
2
2016-01-04

1999-01-04

341234567
LA

Adult
Richard
Alvin
Bingham
90 Highland Place

Baton Rouge
LA
70810
2255559855
M
6′ 2″
250.00
Brown
Brown
true

1994-01-10

1954-01-19

341678901
LA

Adult
Kirk
Richard
Abelson
226 Valley Street

Baton Rouge
LA
70810
2255552234
M
5′ 10″
170.00
Black
Brown
true
5
2015-05-31

1995-05-30

341789012
LA

Adult
Eric
Thomas
Abelson
226 Valley Street

Baton Rouge
LA
70810
2255555542
M
6′ 2″
225.00
Blonde
Blue
true
2
1990-06-20

1970-06-27

341876543
LA

Minor
Bruce
David
Callander
2 Kron Lane

Baton Rouge
LA
70810
2255557875
M
6′ 1″
195.00
Black
Blue
false
2
2016-08-07

1999-08-07

341890123
LA

Adult
Lawrence
Allen
Alexander
198 Broad Street
#1001
Baton Rouge
LA
70810
2255552530
M
6′ 1″
270.00
Gray
Blue
true

2002-02-01

1952-02-01

341901234
LA

Special
Mark
Wayne
Anderson
1459 River Drive

Baton Rouge
LA
70810
2255553964
M
5′ 8″
160.00
Brown
Brown
false
1
1998-12-02

1948-12-22

341987654
LA

Special
Ibrahim
Isaac
Brown
177 Forest Avenue

Baton Rouge
LA
70810
2255551232
M
5′ 10″
200.00
Gray
Brown
true
2
1995-08-10

1935-08-13

342012345
LA

Minor
Andrew
Edward
JAMES
243 Oak

Baton Rouge
LA
70817
2255555833
M
5′ 10″
180
Brown
Brown
true

2016-06-30

1999-05-30

342098765
LA

Minor
Andrea
Marie
LARRABEE
823 Shyrock Drive
#1540
Baton Rouge
LA
70817
2255556789
F
5′ 6″
150.00
Brown
Hazel
false

2016-06-02

1999-04-01

342123456
LA

Minor
Harold
B
STAUSS
One Front Royal

Baton Rouge
LA
70817
2255552176
M
6′ 1″
185
Blonde
Brown
false
2
2016-08-15

1999-03-15

342234567
LA

Minor
David
N
ANDERSON
805 North Allen

Baton Rouge
LA
70817
2255555589
M
6′ 4″
305
Brown
Brown
true

2016-06-04

1999-01-20

342678901
LA

Minor
Kerry
Eldon
MITCHELL
622 Valley View

Baton Rouge
LA
70817
2255554322
M
5′ 10″
170.00
Black
Brown
true

2016-06-01

1999-05-03

342789012
LA

Minor
John
Eric
LANDERS
1032 North Jackson
#301
Baton Rouge
LA
70817
2255552455
M
6′ 2″
225.00
Blonde
Blue
true

2016-06-02

2000-04-18

InsuranceID
DriverID
InsuranceAgentID
AutoType
AutoYear
TagID
TagExpiration
mSysRowId

10001
341012345
SF4485
Buick
2009
4321R
2021-08-01

10002
341234567
AS8842
Nissan
2006
4457D
2021-02-01

10003
341789012
SF8567
Dodge
2003
54687
2021-07-01

10004
341123456
AS8842
Volvo
2010
B3847
2021-02-01

10005
341876543
SF8870
Cadillac
2006
CADME
2021-08-16

10006
342234567
AS2234
Mercedes
2007
MYCAR
2021-06-30

10007
341098765
SF8567
GMC
2008
TR876
2021-10-01

10008
341789012
SF8567
Chrysler
2005
TR887

10009
123456789
AS2234
Toyota
2004
X1234

ID
mSysRowId

1
as0+k/8rEFYPR4o8pew1tX235Q/xsj0pfvB0kKowZzw=-~ydEnff2Tibtnl0ri8w8H+Q==

SELECT [Test Drivers].*
FROM [Test Drivers];

PARAMETERS __DriverID Value;
SELECT DISTINCTROW *
FROM Insurance AS Drivers
WHERE ([__DriverID] = DriverID);

SELECT [Test Drivers].*
FROM [Test Drivers];

SELECT [Test Drivers].*
FROM [Test Drivers];

SELECT DISTINCTROW *
FROM Drivers;

SELECT DISTINCTROW *
FROM Insurance;

SELECT DISTINCTROW *
FROM Drivers;

SELECT DISTINCTROW *
FROM Insurance;

SELECT DISTINCTROW *
FROM Insurance;