Exp19_Access_AppCapstone_Comp – Drivers 1.0

Our academic writers are ready and waiting to assist with any assignment you may have. From simple essays to full dissertations, you're guaranteed we've got a writing expert to perfectly match your needs.


Order a Similar Paper Order a Different Paper

Exp19_Access_AppCapstone_Comp – Drivers 1.0

Exp19 Access AppCapstone Comp Drivers 1.0 

Access Application Capstone – Comp Drivers

  

Project Description:

In this project, you will add fields to a table and set data validation rules. You will also import a text file into a database, design advanced queries, and create a navigation form. Additionally, you will use SQL to modify a record source and create an embedded macro to automate opening a report. Finally, you will create a pie chart in a report.

     

Start   Access. Open the file named Exp19_Access_AppCapstone_Comp_Drivers.   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.

 

You   have discovered that the insurance company data that you want is stored in an   external text file, so you will import the file as a table in the database,   and not need to design the table from scratch.
 

  Create a table in the database by importing the downloaded delimited text   file named Insurance_Text.txt. Use   the first row of the file as field names, use InsuranceID as the primary key, and then name the table InsuranceCos_Text. Accept all other default options. Do   not save the import steps.

 

You   will modify a table to add fields that store the websites and photos of   agents, add a lookup field, and set data validation to ensure that valid   values are entered in a field.
 

  Create a new field in the Agency Info   table after InsPhone named Website with the Hyperlink <br>data type. Save the table. In Datasheet view,   add the website http://William_Smith.com   to the
William Smith record (Record 1).

 

Create   a new field in the Agency Info table after Website named AgentPhoto   with the Attachment data type.   Save the table. In Datasheet view for Record 1 (William Smith), add the downloaded picture file named WmSmith.jpg to the AgentPhoto field.

 

Set   the validation rule of the InsuranceCo   field to accept the values AS,   NAT, or SF   only. Set the validation text to read Please enter AS,   NAT, or SF. (include the period).

 

Make   InsuranceCo a lookup field in the   Agency Info table. Set the lookup to get values from the InsuranceID field in the InsuranceCos_Text table. Accept all   other defaults and save the table. In Datasheet view, select the first InsuranceCo value, type AT and press ENTER. Click OK in the   message box, press ESC, and then close the table.

 

You   will create queries to locate records that have missing values, update values   in a table, and delete specific records from the database.
 

  Create a new query using Design view. From the Insurance table, add the DriverID, AutoType, TagID, and TagExpiration fields (in that order). Save the query as Missing Tag Dates.

 

Set   the criteria in the TagExpiration   field to find null values. Run the query (two records will display). Save   and close the query.

 

Create   a new query using Design view. From the Drivers table, add the Class field. Change the query type to   Update and set the criteria to   update drivers whose class is Minor   to Junior. Run the query (eight records will   update). Run the query again and note that there are no records to update.   Save the query as Driver Class_Update   and close the query. View the updates in the Drivers table and close the   table.

 

Create   a new query using Design view. From the Drivers table, add the Class field. Save the query as Driver Class_Delete.

 

Change   the query type to Delete and set   the criteria to delete drivers whose class is Special.   Run the query (one record will be deleted). Save and close the query. View   the changes in the Drivers table and close the table.

 

Create   a new query using Design view. From the Insurance table, add the InsuranceAgentID, AutoType, AutoYear, and TagID fields   (in that order). Save the query as Agent_Parameter.

 

Set   the criteria in the InsuranceAgentID   field to display the prompt as Enter the Agent   ID:   and run the query. In the parameter prompt, enter AS8842   and click OK to view the results   (two records). Save and close the query.

 

The   Performance Analyzer makes suggestions as to how a selected table can be made   more efficient or to work better with other tables in the database.
 

  Use the Analyze Performance tool to analyze the Drivers table. Note the idea   to change the data type of the Weight field from Short Text to Long Integer.   In the Drivers table, set the data type of the Weight field to Number   (Long Integer), then save and close the table.

 

A   navigation form enables users to select key objects in the database in a   user-friendly manner. The form will launch automatically to present the   frequently used forms and report to the end users.
 

  Create a Navigation form based on   the Vertical Tabs, Left template. Drag and drop the Drivers form onto the first tab of   the form. Drop the Insurance form   onto the second tab.

 

Drag   and drop the Drivers report onto   the third tab of the Navigation form. View the form in Form view, click each   of the tabs, and then save the form as Navigator.   Close the form.

 

Set   the option in the database so that the Navigator   form launches automatically when the database opens. Close the database   and reopen it to ensure that the Navigator form opens. Close the form.

 

You   will modify the record source of a report to display specific records. The   report will not rely on an underlying query to select records; the criteria are   handled by the SQL statement. You will add a command button to a form that   will open a form with associated records.
 

  Open the Drivers report in Design view. Modify the record source of the   report using a SQL statement to select all Drivers   records with a Class   of Adult. Print Preview the report (eight   records will display).

 

Modify   the SQL record source to display Junior   drivers. Print Preview the report (eight records will display). Save and   close the report.

 

You   will add a command button to a form that will open a form with associated   records.
 

  Open the Drivers form in Design view, click to add a command button at the intersection of the 6-inch mark on the   horizontal ruler and the 3-inch mark on the vertical ruler.

 

Set   the command button to open the Insurance form. Use the default picture as the   button. Set the name and the caption of the button to Open Insurance Form. Set the width of the button to 1.5″. Save the form. View the form in Form   view and click the command button.

 

You   will create a chart in a report that compares the number of licenses that   were issued by year. The chart will graphically depict the mix of drivers   according to the years of driving experience.
 

  Open the Auto Year report in   Design view. Click the Report Footer   section bar. Open the Property Sheet and set the height of the Report   Footer section to 3″. Close   the Property Sheet. Click in the blank   space below the Count function.

 

Insert   a pie chart by clicking at the 0.5″ mark on the vertical ruler and the   2.5″ mark on the horizontal ruler. In the Chart Settings pane, set the   Auto Year_Parameter query as the   data source. In the Axis (Category), select the AutoType check box, then in the parameter prompt, type 2012. Close the Chart Settings pane.

 

Click   in the Chart Title box in the   Property Sheet, and type Auto Type.   Close the Property sheet, then save and close the report. Reopen the report   and type 2010   at the prompts. Note that the data in the report and chart displays according   to the year input. Close the report.

 

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

Insurance_Text.txt

InsuranceID InsuranceName
AS AllStates Insurance Co.
SF State Farmers Inc.
NAT National Insurers Inc.

WmSmith.jpg

Exp19_Access_AppCapstone_Comp – Drivers_Instructions.docx

Grader – Instructions Access 2019 Project


Exp19_Access_AppCapstone_Comp – Drivers 1.0

Project Description:

In this project, you will add fields to a table and set data validation rules. You will also import a text file into a database, design advanced queries, and create a navigation form. Additionally, you will use SQL to modify a record source and create an embedded macro to automate opening a report. Finally, you will create a pie chart in a report.

Steps to Perform:

Step

Instructions

Points Possible

1

Start Access. Open the file named
Exp19_Access_AppCapstone_Comp_Drivers. 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

You have discovered that the insurance company data that you want is stored in an external text file, so you will import the file as a table in the database, and not need to design the table from scratch.

Create a table in the database by importing the downloaded delimited text file named
Insurance_Text.txt. Use the first row of the file as field names, use
InsuranceID as the primary key, and then name the table
InsuranceCos_Text. Accept all other default options. Do not save the import steps.

6

3

You will modify a table to add fields that store the websites and photos of agents, add a lookup field, and set data validation to ensure that valid values are entered in a field.

Create a new field in the
Agency Info table after
InsPhone named
Website with the
Hyperlink <br>data type. Save the table. In Datasheet view, add the website
http://William_Smith.com to the

William Smith record (Record 1).

6

4

Create a new field in the Agency Info table after
Website named
AgentPhoto with the
Attachment data type. Save the table. In Datasheet view for Record 1 (
William Smith), add the downloaded picture file named
WmSmith.jpg to the
AgentPhoto field.

6

5

Set the validation rule of the
InsuranceCo field to accept the values
AS,
NAT, or
SF only. Set the validation text to read
Please enter AS, NAT, or SF. (include the period).

7

6

Make
InsuranceCo a lookup field in the Agency Info table. Set the lookup to get values from the
InsuranceID field in the InsuranceCos_Text table. Accept all other defaults and save the table. In Datasheet view, select the first
InsuranceCo value, type
AT and press
ENTER. Click
OK in the message box, press ESC, and then close the table.

6

7

You will create queries to locate records that have missing values, update values in a table, and delete specific records from the database.

Create a new query using Design view. From the Insurance table, add the
DriverID,
AutoType,
TagID, and
TagExpiration
fields (in that order). Save the query as
Missing Tag Dates.

5

8

Set the criteria in the
TagExpiration field to find null values. Run the query (two records will display). Save and close the query.

5

9

Create a new query using Design view. From the Drivers table, add the
Class field. Change the query type to
Update and set the criteria to update drivers whose class is
Minor to
Junior. Run the query (eight records will update). Run the query again and note that there are no records to update. Save the query as
Driver Class_Update and close the query. View the updates in the Drivers table and close the table.

9

10

Create a new query using Design view. From the Drivers table, add the
Class field. Save the query as
Driver Class_Delete.

4

11

Change the query type to
Delete and set the criteria to delete drivers whose class is
Special. Run the query (one record will be deleted). Save and close the query. View the changes in the Drivers table and close the table.

6

12

Create a new query using Design view. From the Insurance table, add the
InsuranceAgentID,
AutoType,
AutoYear, and
TagID fields (in that order). Save the query as
Agent_Parameter.

5

13

Set the criteria in the
InsuranceAgentID field to display the prompt as
Enter the Agent ID: and run the query. In the parameter prompt, enter
AS8842 and click
OK to view the results (two records). Save and close the query.

5

14

The Performance Analyzer makes suggestions as to how a selected table can be made more efficient or to work better with other tables in the database.

Use the Analyze Performance tool to analyze the Drivers table. Note the idea to change the data type of the Weight field from Short Text to Long Integer. In the Drivers table, set the data type of the
Weight field to
Number (Long Integer), then save and close the table.

3

15

A navigation form enables users to select key objects in the database in a user-friendly manner. The form will launch automatically to present the frequently used forms and report to the end users.

Create a
Navigation form based on the
Vertical Tabs, Left
template. Drag and drop the
Drivers form onto the first tab of the form. Drop the
Insurance form onto the second tab.

3

16

Drag and drop the
Drivers report onto the third tab of the Navigation form. View the form in Form view, click each of the tabs, and then save the form as
Navigator. Close the form.

2

17

Set the option in the database so that the
Navigator form launches automatically when the database opens. Close the database and reopen it to ensure that the Navigator form opens. Close the form.

2

18

You will modify the record source of a report to display specific records. The report will not rely on an underlying query to select records; the criteria are handled by the SQL statement. You will add a command button to a form that will open a form with associated records.

Open the Drivers report in Design view. Modify the record source of the report using a SQL statement to select all
Drivers records with a
Class of
Adult. Print Preview the report (eight records will display).

2

19

Modify the SQL record source to display
Junior drivers. Print Preview the report (eight records will display). Save and close the report.

3

20

You will add a command button to a form that will open a form with associated records.

Open the Drivers form in Design view, click to add a
command button at the intersection of the 6-inch mark on the horizontal ruler and the 3-inch mark on the vertical ruler.

3

21

Set the command button to open the Insurance form. Use the default picture as the button. Set the name and the caption of the button to
Open Insurance Form. Set the width of the button to
1.5″. Save the form. View the form in Form view and click the
command button.

5

22

You will create a chart in a report that compares the number of licenses that were issued by year. The chart will graphically depict the mix of drivers according to the years of driving experience.

Open the
Auto Year report in Design view. Click the
Report Footer section bar. Open the Property Sheet and set the height of the Report Footer section to
3″. Close the Property Sheet. Click in the
blank space below the Count function.

2

23

Insert a pie chart by clicking at the
0.5″
mark on the vertical ruler and the
2.5″
mark on the horizontal ruler. In the Chart Settings pane, set the
Auto Year_Parameter query as the data source. In the Axis (Category), select the
AutoType check box, then in the parameter prompt, type
2012. Close the Chart Settings pane.

3

24

Click in the
Chart Title box in the Property Sheet, and type
Auto Type. Close the Property sheet, then save and close the report. Reopen the report and type
2010 at the prompts. Note that the data in the report and chart displays according to the year input. Close the report.

2

25

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

0

Total Points

100

Created On: 03/06/2020 1 Exp19_Access_AppCapstone_Comp – Drivers

Exp19_Access_AppCapstone_Comp_Drivers.accdb

AgentID InsuranceCo InsAgentFirstName InsAgentLastName InsAddress InsCity InsState InsZIP InsPhone mSysRowId
AS2234 AS William Smith 7035556657
AS8842 AS Anna Taylor 7035559998
SF4485 SF Terri Jones 7035558876
SF8567 SF James Johnson 7035551243
SF8870 SF Darin Miller 7035550987
DriverID StateIssued Photo Class FirstName MiddleName LastName Street Apartment City State ZIP HomePhone Gender Height Weight HairColor EyeColor OrganDonor Restrictions IssueDate ExpirationDate DateOfBirth mSysRowId
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 2021-01-04 2005-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 1995-05-13 1945-05-30
341789012 LA Adult Eric Thomas Abrahamson 130 Flay Street #301 Baton Rouge LA 70810 2255555542 M 6′ 2″ 225.00 Blonde Blue true 2 2000-06-20 1980-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 2021-08-07 2005-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 Adult 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 1975-08-13
342012345 LA Minor Andrew Edward JAMES 243 Oak Baton Rouge LA 70817 2255555833 M 5′ 10″ 180 Brown Brown true 2021-06-30 2005-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 2022-06-02 2006-04-01
342123456 LA Minor Harold B STAUSS One Front Royal Baton Rouge LA 70817 2255552176 M 6′ 1″ 185 Blonde Brown false 2 2022-08-15 2006-03-15
342234567 LA Minor David N ANDERSON 805 North Allen Baton Rouge LA 70817 2255555589 M 6′ 4″ 305 Brown Brown true 2021-06-04 2005-01-20
342678901 LA Minor Kerry Eldon MITCHELL 622 Valley View Baton Rouge LA 70817 2255554322 M 5′ 10″ 170.00 Black Brown true 2022-06-01 2005-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 2021-06-02 2005-04-18
InsuranceID DriverID InsuranceAgentID AutoType AutoYear TagID TagExpiration mSysRowId
10009 123456789 AS2234 Toyota 2009 X1234
10001 341012345 SF4485 Buick 2011 4321R 2022-08-01
10007 341098765 SF8567 GMC 2010 TR876 2022-10-01
10004 341123456 AS8842 Volvo 2012 B3847 2022-02-01
10002 341234567 AS8842 Nissan 2008 4457D 2022-02-01
10008 341789012 SF8567 Chrysler 2010 TR887
10005 341876543 SF8870 Cadillac 2011 CADME 2022-08-16
10003 341789012 SF8567 Dodge 2008 54687 2022-07-01
10006 342234567 AS2234 Mercedes 2012 MYCAR 2022-03-01
ID mSysRowId
1
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;
SELECT Insurance.DriverID, Insurance.AutoType, Insurance.AutoYear, Insurance.TagID
FROM Insurance
WHERE (((Insurance.AutoYear)=[Enter the auto year:]));

Writerbay.net

Do you need help with this or a different assignment? In a world where academic success does not come without efforts, we do our best to provide the most proficient and capable essay writing service. After all, impressing professors shouldn’t be hard, we make that possible. If you decide to make your order on our website, you will get 15 % off your first order. You only need to indicate the discount code GET15.


Order a Similar Paper Order a Different Paper