Insurance and drivers

Peralta_Exp19_Access_Ch6_Cap_Insurance.accdb

Home >Computer Science homework help >Insurance and drivers

AgentID	InsuranceCo	InsAgentFirstName	InsAgentLastName	InsPhone	mSysRowId
AS2234	AS	William	Smith	7035556657	248X1Cg+5OKIJ/NdKsO0sJFfAxF17H2IBEhbB6q8PDA=-~/Ji3VT7bbTg+5EhpUR5UHg==
AS8842	AS	Anna	Taylor	7035559998	248X1Cg+5OKIJ/NdKsO0sJFfAxF17H2IBEhbB6q8PDA=-~/Ji3VT7bbTg+5EhpUR5UHg==
SF4485	SF	Terri	Jones	7035558876	248X1Cg+5OKIJ/NdKsO0sJFfAxF17H2IBEhbB6q8PDA=-~/Ji3VT7bbTg+5EhpUR5UHg==
SF8567	SF	James	Johnson	7035551243	248X1Cg+5OKIJ/NdKsO0sJFfAxF17H2IBEhbB6q8PDA=-~/Ji3VT7bbTg+5EhpUR5UHg==
SF8870	SF	Darin	Miller	7035550987	248X1Cg+5OKIJ/NdKsO0sJFfAxF17H2IBEhbB6q8PDA=-~/Ji3VT7bbTg+5EhpUR5UHg==

DriverID	StateIssued	Class	FirstName	MiddleName	LastName	Street	Apartment	City	State	ZIP	HomePhone	Gender	Height	Weight	HairColor	EyeColor	OrganDonor	Restrictions	IssueDate	DateOfBirth
123456789	LA	Adult	Pamela	R	Toliver	7015 Westchester Drive		Baton Rouge	LA	70810	2255552752	F	5' 7"	135	Blonde	Brown	true	2	7/5/08	11/22/85
341012345	LA	Adult	Brian	Andrew	Andrews	132 Blue Street		Baton Rouge	LA	70810	2255553385	M	5' 10"	200.00	Brown	Brown	true	4	5/5/95	5/15/75
341098765	LA	Adult	Victoria	May	Bowman	20 Spruce Avenue		Baton Rouge	LA	70810	2255559876	F	5' 5"	130.00	Red	Hazel	false	3	6/10/99	6/20/69
341234567	LA	Adult	Richard	Alvin	Bingham	90 Highland Place		Baton Rouge	LA	70810	2255559855	M	6' 2"	250.00	Brown	Brown	true		1/10/94	1/19/54
341678901	LA	Adult	Kirk	Richard	Abelson	226 Valley Street		Baton Rouge	LA	70810	2255552234	M	5' 10"	170.00	Black	Brown	true	5	5/31/15	5/30/95
341789012	LA	Adult	Eric	Thomas	Abelson	226 Valley Street		Baton Rouge	LA	70810	2255555542	M	6' 2"	225.00	Blonde	Blue	true	2	6/20/90	6/27/70
341890123	LA	Adult	Lawrence	Allen	Alexander	198 Broad Street	#1001	Baton Rouge	LA	70810	2255552530	M	6' 1"	270.00	Gray	Blue	true		2/1/02	2/1/52

InsuranceID	DriverID	InsuranceAgentID	AutoType	AutoYear	TagID	TagExpiration
10001	341012345	SF4485	Buick	2009	4321R	8/1/21
10002	341234567	AS8842	Nissan	2006	4457D	2/1/21
10003	341789012	SF8567	Dodge	2003	54687	7/1/21
10004	341123456	AS8842	Volvo	2010	B3847	2/1/21
10005	341876543	SF8870	Cadillac	2006	CADME	8/16/21
10006	342234567	AS2234	Mercedes	2007	MYCAR	6/30/21
10007	341098765	SF8567	GMC	2008	TR876	10/1/21
10008	341789012	SF8567	Chrysler	2005	TR887
10009	123456789	AS2234	Toyota	2004	X1234
10010	341890123	AS8842	GMC	2004	TR887

ID	mSysRowId
1	9uuyhKMivaROBMMMI2ryfPXMhA5xyCW2mglE2jDEHd4=-~bNWx2LY+oFuH3fErq6ySvw==

ID	mSysRowId
1	tpkH7MDvhLychvTnJl5tlJ/hYcxMjdjZLAAdDfN2Q9w=-~1nEHKPOIf75GCE3Pj3ZRbA==

DriverID	StateIssued	Class	FirstName	MiddleName	LastName	Street	Apartment	City	State	ZIP	HomePhone	Gender	Height	Weight	HairColor	EyeColor	OrganDonor	Restrictions	IssueDate	DateOfBirth
341123456	LA	Minor	James	Hallard	Barnes	8 Shane Drive		Baton Rouge	LA	70810	2255556712	Male	6' 1"	205.00	Blonde	Brown	false	2	1/4/16	1/4/99
341876543	LA	Minor	Bruce	David	Callander	2 Kron Lane		Baton Rouge	LA	70810	2255557875	Male	6' 1"	195.00	Black	Blue	false	2	8/7/16	8/7/99
341901234	LA	Senior	Mark	Wayne	Anderson	1459 River Drive		Baton Rouge	LA	70810	2255553964	Male	5' 8"	160.00	Brown	Brown	false	1	12/2/98	12/22/48
341987654	LA	Senior	Ibrahim	Isaac	Brown	177 Forest Avenue		Baton Rouge	LA	70810	2255551232	Male	5' 10"	200.00	Gray	Brown	true	2	8/10/95	8/13/35
342012345	LA	Minor	Andrew	Edward	JAMES	243 Oak		Baton Rouge	LA	70817	2255555833	Male	5' 10"	180	Brown	Brown	true		6/30/16	5/30/99
342098765	LA	Minor	Andrea	Marie	LARRABEE	823 Shyrock Drive	#1540	Baton Rouge	LA	70817	2255556789	Female	5' 6"	150.00	Brown	Hazel	false		6/2/16	4/1/99
342123456	LA	Minor	Harold	B	STAUSS	One Front Royal		Baton Rouge	LA	70817	2255552176	Male	6' 1"	185	Blonde	Brown	false	2	8/15/16	3/15/99
342234567	LA	Minor	David	N	ANDERSON	805 North Allen		Baton Rouge	LA	70817	2255555589	Male	6' 4"	305	Brown	Brown	true		6/4/16	1/20/99
342678901	LA	Minor	Kerry	Eldon	MITCHELL	622 Valley View		Baton Rouge	LA	70817	2255554322	Male	5' 10"	170.00	Black	Brown	true		6/1/16	5/3/99
342789012	LA	Minor	John	Eric	LANDERS	1032 North Jackson	#301	Baton Rouge	LA	70817	2255552455	Male	6' 2"	225.00	Blonde	Blue	true		6/2/16	4/18/00

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 DISTINCTROW * FROM Drivers;

SELECT DISTINCTROW * FROM Insurance;

SELECT DISTINCTROW * FROM Drivers;

SELECT DISTINCTROW * FROM Insurance;

DELETE Drivers.DriverID, Drivers.StateIssued, Drivers.Photo, Drivers.Class, Drivers.FirstName, Drivers.MiddleName, Drivers.LastName, Drivers.Street, Drivers.Apartment, Drivers.City, Drivers.State, Drivers.ZIP, Drivers.HomePhone, Drivers.Gender, Drivers.Height, Drivers.Weight, Drivers.HairColor, Drivers.EyeColor, Drivers.OrganDonor, Drivers.Restrictions, Drivers.IssueDate, Drivers.ExpirationDate, Drivers.DateOfBirth FROM Drivers WHERE (((Drivers.Class)="Minor")) OR (((Drivers.Class)="Senior"));

INSERT INTO [Non-Standard Drivers] (DriverID, StateIssued, Photo, Class, FirstName, MiddleName, LastName, Street, Apartment, City, State, ZIP, HomePhone, Gender, Height, Weight, HairColor, EyeColor, OrganDonor, Restrictions, IssueDate, ExpirationDate, DateOfBirth) SELECT Drivers.DriverID, Drivers.StateIssued, Drivers.Photo, Drivers.Class, Drivers.FirstName, Drivers.MiddleName, Drivers.LastName, Drivers.Street, Drivers.Apartment, Drivers.City, Drivers.State, Drivers.ZIP, Drivers.HomePhone, Drivers.Gender, Drivers.Height, Drivers.Weight, Drivers.HairColor, Drivers.EyeColor, Drivers.OrganDonor, Drivers.Restrictions, Drivers.IssueDate, Drivers.ExpirationDate, Drivers.DateOfBirth FROM Drivers WHERE (((Drivers.Class)="Minor"));

SELECT Drivers.DriverID, Drivers.StateIssued, Drivers.Photo, Drivers.Class, Drivers.FirstName, Drivers.MiddleName, Drivers.LastName, Drivers.Street, Drivers.Apartment, Drivers.City, Drivers.State, Drivers.ZIP, Drivers.HomePhone, Drivers.Gender, Drivers.Height, Drivers.Weight, Drivers.HairColor, Drivers.EyeColor, Drivers.OrganDonor, Drivers.Restrictions, Drivers.IssueDate, Drivers.ExpirationDate, Drivers.DateOfBirth FROM Drivers LEFT JOIN Insurance ON Drivers.[DriverID] = Insurance.[DriverID] WHERE (((Insurance.DriverID) Is Null));

SELECT Drivers.[LastName], Drivers.[Street], Drivers.[DriverID], Drivers.[FirstName] FROM Drivers WHERE (((Drivers.[LastName]) In (SELECT [LastName] FROM [Drivers] As Tmp GROUP BY [LastName],[Street] HAVING Count(*)>1 And [Street] = [Drivers].[Street]))) ORDER BY Drivers.[LastName], Drivers.[Street];

SELECT Drivers.DriverID, Drivers.StateIssued, Drivers.Photo, Drivers.Class, Drivers.FirstName, Drivers.MiddleName, Drivers.LastName, Drivers.Street, Drivers.Apartment, Drivers.City, Drivers.State, Drivers.ZIP, Drivers.HomePhone, Drivers.Gender, Drivers.Height, Drivers.Weight, Drivers.HairColor, Drivers.EyeColor, Drivers.OrganDonor, Drivers.Restrictions, Drivers.IssueDate, Drivers.ExpirationDate, Drivers.DateOfBirth INTO [Non-Standard Drivers] FROM Drivers WHERE (((Drivers.Class)="Senior"));

TRANSFORM Count([Non-Standard Drivers].DriverID) AS CountOfDriverID SELECT [Non-Standard Drivers].Class, Count([Non-Standard Drivers].DriverID) AS [Total Of DriverID] FROM [Non-Standard Drivers] WHERE ((([Non-Standard Drivers].Gender) Like "F"="Female")) OR (("M"="Male")) GROUP BY [Non-Standard Drivers].Class PIVOT [Non-Standard Drivers].Gender;

UPDATE Drivers SET Drivers.Class = "Senior" WHERE (((Drivers.Class)="Special"));