Drivers and Insurance
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.
For the purpose of grading the project you are required to perform the following tasks:
Start Access. Open the file namedexploring_a_capstone_grader.accdb. Save the database asexploring_a_capstone_grader_LastFirst.
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.
Create a new field in the Agency Info table after InsPhone named Web site with the Hyperlink data type. Save the table. In Datasheet view, add the Web sitehttp://William_Smith.com to the William Smith record (1).
Create a new field in the Agency Info table after Web site named AgentPhoto with the Attachment data type. Save the table. In Datasheet view for Record 1 (William Smith), add the downloaded picture file named a00c2WmSmith.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 readPlease 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, click in any InsuranceCo cell and click the arrow to view the options. Close the table.
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.