Exp19_Access_Ch6_Cap_Insurance

irst

ame

owId

2

AS

556657

vgCW8k=-~YPWLKPp+3v0a07ZH3ChOvg==

AS

E8QsH/keys7sazaOJTUVGCUxYzF+RQTNqzXrBvgCW8k=-~YPWLKPp+3v0a07ZH3ChOvg==

4485

SF

E8QsH/keys7sazaOJTUVGCUxYzF+RQTNqzXrBvgCW8k=-~YPWLKPp+3v0a07ZH3ChOvg==

SF

son

243

E8QsH/keys7sazaOJTUVGCUxYzF+RQTNqzXrBvgCW8k=-~YPWLKPp+3v0a07ZH3ChOvg==

SF

iller

E8QsH/keys7sazaOJTUVGCUxYzF+RQTNqzXrBvgCW8k=-~YPWLKPp+3v0a07ZH3ChOvg==

																																																																													Agent Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	ID	InsuranceCo	InsAgent				F	N	InsAgent	LastName	InsAddress	Ins Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	City	Ins	State	Ins	ZIP	InsPhone			mSys	R
		AS						2	3	4	William	Smith	703	5					E8QsH/keys7sazaOJTUVGCUxYzF+RQTNqzXr	B
		AS8842	Anna	Taylor	7035559998
			SF	Terri	Jones	7035558876
			SF8567		James		John	703555		1
	SF8870	Darin															M	7035550987

FirstName

LastName

City
State
ZIP

R

LA

F

2

-07-05

LA

Adult

Street

Baton Rouge
LA
70810

M

Brown
Brown
true
4

LA

Adult

Baton Rouge
LA
70810

F

3

LA

James

Baton Rouge
LA
70810

M

Blonde
Brown
false
2

LA

Adult

Baton Rouge
LA
70810

M

Brown
Brown
true

LA

Adult

Richard

Baton Rouge
LA
70810

M
5′ 10″

Brown
true
5

LA

Adult

Abelson
226 Valley Street

Baton Rouge
LA
70810

M
6′ 2″

Blonde
Blue
true
2

LA

Minor

Baton Rouge
LA
70810

M
6′ 1″

Black
Blue
false
2

LA

Adult

Baton Rouge
LA
70810

M
6′ 1″

Blue
true

LA

Baton Rouge
LA
70810

M

Brown
Brown
false
1

LA

Special

Brown

Baton Rouge
LA
70810

M
5′ 10″
200.00
Gray
Brown
true
2

LA

Minor
Andrew

Baton Rouge
LA

M
5′ 10″

Brown
Brown
true

LA

Minor

Baton Rouge
LA
70817

F

Brown
Hazel
false

LA

Minor

B

Baton Rouge
LA
70817

M
6′ 1″

Blonde
Brown
false
2

LA

Minor
David
N

Baton Rouge
LA
70817

M

Brown
Brown
true

LA

Minor

Baton Rouge
LA
70817

M
5′ 10″
170.00
Black
Brown
true

LA

Minor
John
Eric

Baton Rouge
LA
70817

M
6′ 2″
225.00
Blonde
Blue
true

2016-06-02

	DriverID	StateIssued	Photo	Class	MiddleName	Street	Apartment	HomePhone	Gender	Height	Weight	HairColor	EyeColor	OrganDonor	Restrictions	IssueDate	ExpirationDate	DateOfBirth
	123456789																																		LA							Adult	Pamela	Toliver	7015 Westchester Drive																	Baton Rouge											70810	2255552752	5′ 7″	135					Blonde																		Brown											true		2008	1985-11-22
	341012345	Brian		Andrew	Andrews	132				Blue	2255553385					5′ 10″		200.00	1995-05-05	1975-05-15
	341098765	Victoria	May	Bowman	20 Spruce Avenue	2255559876	5′ 5″	130.00	Red		Hazel						false	1999-06-10	1969-06-20
	341123456								Minor	Hallard	Barnes	8 Shane Drive	2255556712				6′ 1″	205.00	2016-01-04	1999-01-04
	341234567		Richard	Alvin	Bingham	90 Highland Place	2255559855			6′ 2″	250.00	1994-01-10	1954-01-19
341678901	Kirk		Abelson		226 Valley Street	2255552234		170.00			Black	2015-05-31	1995-05-30
		341789012		Eric	Thomas	2255555542		225.00	1990-06-20	1970-06-27
	341876543	Bruce		David	Callander	2 Kron Lane	2255557875	195.00	2016-08-07	1999-08-07
341890123	Lawrence	Allen	Alexander	198 Broad Street	#1001	2255552530	270.00		Gray	2002-02-01	1952-02-01
341901234		Special	Mark	Wayne	Anderson	1459 River Drive	2255553964	5′ 8″	160.00	1998-12-02	1948-12-22
341987654	Ibrahim	Isaac	177 Forest Avenue	2255551232	1995-08-10	1935-08-13
342012345	Edward	JAMES	243 Oak						70817	2255555833	180	2016-06-30	1999-05-30
342098765	Andrea	Marie	LARRABEE	823 Shyrock Drive	#1540	2255556789	5′ 6″	150.00		2016-06-02	1999-04-01
342123456	Harold	STAUSS	One Front Royal	2255552176	185	2016-08-15	1999-03-15
	342234567	ANDERSON	805 North Allen	2255555589	6′ 4″	305	2016-06-04	1999-01-20
342678901	Kerry	Eldon	MITCHELL	622 Valley View	2255554322	2016-06-01	1999-05-03
342789012	LANDERS	1032 North Jackson	#301	2255552455	2000-04-18

DriverID

mSysRowId

341012345

341234567
AS8842

341789012
SF8567

341123456
AS8842

2021-02-01

341876543
SF8870

2006

342234567

341098765
SF8567

2008

341789012
SF8567

123456789
AS2234

InsuranceID	InsuranceAgentID	AutoType	AutoYear	TagID	TagExpiration
10001	SF4485	Buick	2009	4321R	2021-08-01
10002	Nissan		2006	4457D		2021-02-01
10003	Dodge	2003	54687	2021-07-01
10004	Volvo	2010	B3847
10005	Cadillac	CADME	2021-08-16
10006		AS2234	Mercedes	2007	MYCAR	2021-06-30
10007	GMC	TR876	2021-10-01
10008	Chrysler	2005	TR887
10009	Toyota	2004	X1234

ID
mSysRowId
1

Pwj2RswsWfGnAP/4k3MU6o8HmB/5f/UAJ5c6DVakZ7M=-~8WAbWfxcqBlWv5UcuiV43Q==

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;

Grader – Instructions Access

2

0

1

9

Project

Exp19_Access_Ch0

6

_Cap – Insurance 1.0

3

>
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