Chat with us, powered by LiveChat Exp19_Access_Ch06_Cap - Insurance 1.0 | Wridemy

Exp19_Access_Ch06_Cap – Insurance 1.0

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.

 

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.

 

Run   the query, save it as Make Non-Standard   Drivers   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.docx

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;

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.

Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.

Do you need an answer to this or any other questions?

About Wridemy

We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.

How It Works

To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.

Are there Discounts?

All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.

Hire a tutor today CLICK HERE to make your first order

Related Tags

Academic APA Writing College Course Discussion Management English Finance General Graduate History Information Justify Literature MLA