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 . 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 . Use the first row of the file as field names, use as the primary key, and then name the table . 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 after named with the data type. Save the table. In Datasheet view, add the website to the record (Record 1). Create a new field in the Agency Info table after named with the . Save the table. In Datasheet view for Record 1 ( ), add the downloaded picture file named to the . Set the validation rule of the to accept the values , , or only. Set the validation text to read (include the period). Make a lookup field in the Agency Info table. Set the lookup to get values from the in the InsuranceCos_Text table. Accept all other defaults and save the table. In Datasheet view, select the first , type and press . Click 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 , , , and (in that order). Save the query as . Set the criteria in the 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 . Change the query type to and set the criteria to update drivers whose class is to . Run the query (eight records will update). Run the query again and note that there are no records to update. Save the query as 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 . Save the query as . Change the query type to and set the criteria to delete drivers whose class is . 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 , , , and (in that order). Save the query as . Set the criteria in the to display the prompt as and run the query. In the parameter prompt, enter and click 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 to , 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 based on the . Drag and drop the onto the first tab of the form. Drop the onto the second tab. Drag and drop the 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 . Close the form. Set the option in the database so that the 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 records with a of Print Preview the report (eight records will display). Modify the SQL record source to display 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 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 . Set the width of the button to . Save the form. View the form in Form view and click the . 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 in Design view. Click the . Open the Property Sheet and set the height of the Report Footer section to . Close the Property Sheet. Click in the below the Count function. Insert a pie chart by clicking at the on the vertical ruler and the on the horizontal ruler. In the Chart Settings pane, set the as the data source. In the Axis (Category), select the , then in the parameter prompt, type . Close the Chart Settings pane. Click in the in the Property Sheet, and type . Close the Property sheet, then save and close the report. Reopen the report and type 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.