BUSI Final

Create a Split form from the InsurancePlans table.

On the Create tab, in the Forms group, click the More Forms button, and select Split Form.

Start a new query in Design view.

On the Create tab, in the Queries group, click the Query Design button.

From Print Preview view, export the Staff Insurance Elections report as a PDF file. Use the suggested location and file name.

On the Print Preview tab, in the Data group, click the PDF or XPS button. Click Publish. Click Close.

Add criteria to this query to return only the records where the value in the Premium field is greater than 200 and less than 250. Run the query to view the results.

Add criteria to this query to return only the records where the value in the Premium field is greater than 200 and less than 250. Run the query to view the results.

Apply the Short Date format to the DOB bound text box controls. Do not switch views.

Click any value in the DOB column. On the Report Layout Tools Format tab, in the Number group, expand the Format list and select Short Date.

Add a new record to this form.

Click the New (blank) record button at the bottom of the form.

Add a new record to the table with the staff ID 10-9999 and advance to the second field.

Click the New (blank) record button at the bottom of the table. Type 10-9999 in the StudentID field and press Tab.

From Design view, modify the IncreaseType field to use a lookup list with Merit and COLA in a single column. Limit the field to values in the list only.

Expand the Data Type list for the IncreaseType field, and select Lookup Wizard... Click the I will type in the values that I want. radio button. Click Next. In the first cell under Col 1, type Merit. Press Tab. Type COLA. Click Next. Click the Limit to List check box. Click Finish.

Modify the table relationships so that when a record is deleted in the Departments table, the related records in the Staff table will also be deleted.

On the Database Tools tab, in the Relationships group, click the Relationships button. Double-click the line connecting the Departments and Staff tables. Click the Cascade Delete Related Records check box. Click OK.

Import data from the Departments comma-delimited text file to a new table. Use the first row as field names. Do not change any field information. Use the DeptID field as the primary key. Accept the new table Departments. Do not save the import.

On the External Data tab, in the Import & Link group, click the New Data Source button, select From File and click Text File. Click the Browse button. Click the Departments file. Click Open. Click OK. Click Next. Click the First Row Contains Field Names check box. Click Next. Click Next. Click the Choose my own primary key radio button. Click Next. Click Finish. Click Close.

From Design view, add a subform control to the bottom of this form. Base the subform on the Staff form. Accept the first suggested link between the form and subform. Finish the subform without changing the subform name.

On the Form Design Tools Design tab, in the Controls group, expand the Controls gallery and click the Subform/Subreport button. Click in the empty area of the Detail section at the bottom of the form. Click the Use an existing form radio button. Click Staff. Click Next. Click Next. Click Finish.

From Design view, add a combo box control to the form to the upper right corner of the Detail section of the form. Create a combo box that can be used as a navigation tool based on the value selected. Include the StaffID and LastName fields, in that order. Keep the primary key hidden. The label should display: Find staff by last name

On the Form Design Tools Design tab, in the Controls group, expand the Controls gallery by clicking the More button. Click the Combo Box button. Click in the form to the right of the DOB field. Click the Find a record on my form based on the value I selected in my combo box. radio button. Click Next. Double-click StaffID. Double-click LastName. Click Next. Click Next. In the box, type Find staff by last name and then click Finish.

Add a subform to the bottom of this form to display all the fields from the WageIncreases table. Accept the relationship suggested by Access, and accept the suggested name for the subform.

On the Form Design Tools Design tab, in the Controls group, expand the Controls gallery by clicking the More button. Click the Subform/Subreport button. Click in the empty area at the bottom of the form. Click Next. Expand the Tables/Queries list and select Table: WageIncreases. Click the >> button. Click Next. Click Next. Click Finish.

Add the image MedicalInsurance to the form header as a logo. Do not switch views.

On the Form Layout Tools Design tab, in the Header/Footer group, click the Logo button. In the Insert Picture dialog, click MedicalInsurance to select the file, and then click the Open button.

From Layout view, apply bold formatting to the selected controls. Do not switch views.

On the Form Layout Tools Format tab, in the Font group, click the Bold button.

Display the Total row for this query.

On the Query Tools Design tab, in the Show/Hide group, click the Totals button.

Change the data type for the LastName field to Short Text.

On the Table Tools Fields tab, in the Formatting group, expand the Data Type list, and select Short Text.

Delete the Copy of Staff table.

Right-click Copy of Staff. Click Delete. Click Yes.

Add criteria to this query to return only the records where the value in the Premium field is <500 and the value in the SubscriptionType field is Family. Run the query to view the results.

Type <500 in the Criteria row in the Premium column. Type Family in the Criteria row in the SubscriptionType column. On the Query Tools Design tab, in the Results group, click the Run button.

Add criteria to this query to return only the records where the value in the SubscriptionType field is Self or Family and the value in the Premium field is <200. Run the query to view the results.

Type >200 and <250 in the Criteria row in the Premium column. On the Query Tools Design tab, in the Results group, click the Run button.

Create a Multiple Items form from the InsurancePlans table.

On the Create tab, in the Forms group, click the More Forms button, and select Multiple Items.

Modify this query so the InsuranceType field is hidden in the query results, and then run the query.

Click the Show box in the InsuranceType column to remove the checkmark. On the Query Tools Design tab, in the Results group, click the Run button.

From Datasheet view, add a new field the far right of the table to store a picture of each staff member. Add the picture JerryWashington to the fifth record.

Click the arrow in the Click to Add column to expand the list of available field data types. Click Attachment. Double-click the Attachment field in the fifth record. The Attachments dialog opens. Click the Add... button. Double-click the JerryWashington file to add it. Click OK.

Add criteria to this query to return only the records where the value in the InsuranceType field is Dental or Vision. Run the query to view the results.

Type Dental in the Criteria row in the InsuranceType column. Type Vision in the or row in the InsuranceType column (below the Criteria row). On the Query Tools Design tab, in the Results group, click the Run button.

From Layout view, add automatic totals to the Premium column to calculate the average premium.

Click any value in the Premium column. On the Report Layout Tools Design tab, in the Grouping & Totals group, click the Totals button. Select Average.

From Design view, change the data type for the StaffID field to use a lookup list. The values in the list should include LastName from the Staff table. Items in the list should sort alphabetically by the LastName field. Allow Access to hide the key column. Enforce data integrity, and do not allow deletions that would violate the integrity of records. Save the table.

Expand the Data Type list for the StaffID field and select Lookup Wizard¿ Click Next. Click Table: Staff. Click Next. Double-click LastName. Click Next. Expand the first sort box, and click LastName. Click Next. Click Next. Click the Enable Data Integrity check box. Click Finish. Click Yes.

Limit the query results to records where the insurance Premium is greater than 250. Run the query to view the results.

Type >250 in the Criteria row in the Premium column. On the Query Tools Design tab, in the Results group, click the Run button.

Create a parameter query where the user will enter a value to use as the criterion for the InsuranceType field. Use the prompt Enter Insurance Type. Run the query using the insurance type dental.

Click in the Criteria row in the InsuranceType column and type [Enter Insurance Type]. Click the Run button. Type dental when prompted. Click OK.

Add a new calculated field named EmployeeCost in the first empty column to the right of the ProviderName field. The new field should calculate the value in the Premium field multiplied by .25. Run the query to view the results.

Click in the first empty cell in the Field row in the query grid to the right of the ProviderName column. On the Query Tools Design tab, in the Query Setup group, click the Builder button to open the Expression Builder. Double-click the Premium field and type *.25 to finish the expression. Click OK. Click in the new field and replace Expr1 with EmployeeCost as the name for the field. On the Query Tools Design tab, in the Results group, click the Run button.

A zipped file has been downloaded. Move all the files in the selected zipped file to the Documents folder under OneDrive.

Click the Compressed Folder Tools Extract tab. Click the Extract All button. Verify the files will be extracted to the C:\Users\Ken Dishner\OneDrive\Documents. Click the Extract button.

Run the Compact & Repair tool to optimize database efficiency.

Click the File tab to open Backstage. Click the Compact & Repair Database button.

Create a backup of the database. You do not need to change the backup location.

Click the File tab. Click Save As. Click Back Up Database in the Save Database As section at the right side of the screen. Click the Save As button. Click Save.

Share the document with [email protected] and include the message Please proofread and edit. The recipient should be able to edit the document.

Click the Share button at the far right side of the Ribbon next to the user profile name. Type the e-mail address in the Invite people box. Verify Can edit is selected. Type the message Please proofread and edit. in the Include a message box. Click the Share button.

Zip the contents of the Finances folder.

Click the Share tab. In the Send group, click the Zip button.

Apply the Currency format to the Premium bound text box controls. Do not switch views.

Click the control that displays the Premium value. On the Form Layout Tools Format tab, in the Number group, click the Currency Format button.

The company no longer offers this insurance plan. Delete this record.

Click the record selector to select the entire record. On the Home tab, in the Records group, click the Delete button. Click Yes.

From Layout view, change the width of the ProviderName column, so the company names do not wrap to two lines (approximately 1.3 inches).

Click the right border of the ProviderName column and drag to the right until the entire name Health Care One is visible on one line.

Use the Report Wizard to create a new report. Include fields in this order: DepartmentName from the Departments table, LastName, FirstName, and DateOfHire from the Staff table. View the report data by the Departments table. Add grouping by DateOfHire. Sort records by DateOfHire. Use the Stepped layout and Portrait orientation. Name the report StaffByDepartment and allow Access to display the report in Print Preview view when you finish.

Expand the Tables/Queries list and select Table: Departments. Double-click DepartmentName. Expand the Tables/Queries list and select Table: Staff. Double-click LastName, FirstName and then DateOfHire. Click Next. Click Next. Click DateOfHire and then click the > button. Click Next. Expand the 1 box and select DateOfHire. Click Next. Click Next. Type StaffByDepartment in the box and click Finish.

Export the InsuranceOptions query to Excel, maintaining all data formatting and layouts. You do not need to change the location of the saved file or save the export steps.

In the Navigation Pane, click the InsuranceOptions query once to select it. On the External Data tab, in the Export group, click the Excel button. Click the Export data with formatting and layout. check box. Click OK. Click Close.

Export the InsuranceOptions query to a comma-separated text file. Include field names in the export. You do not need to change the location of the saved file or save the export steps.

In the Navigation Pane, click the InsuranceOptions query once to select it. On the External Data tab, in the Export group, click the Text File button. Click OK. Click Next. Click the Include Field Names on First Row check box. Click Next. Click Finish. Click Close.

Add criteria to this summary query to include only records where the value in the DepartmentName field is Management. Do not include the DepartmentName field in the query groups. Run the query.

In the Total row, under DepartmentName, expand the Group by list and select Where. In the Criteria row, under DepartmentName, type Management. On the Query Tools Design tab, in the Results group, click the Run button.

Use the Form Wizard to create a new form. Include these fields in this order from the Staff table: StaffID, LastName, FirstName, and MedicalInsurance and then add these fields from the InsurancePlans table: InsuranceID and Premium. Organize the form by InsurancePlans with data from the Staff table as a datasheet subform. Accept the suggested names for the form and subform. Allow Access to open the form in Form view when you are finished.

On the Create tab, in the Forms group, click the Form Wizard button. Expand the Tables/Queries list and select Table: Staff. Double-click these fields in order: StaffID, LastName, FirstName, and MedicalInsurance. Expand the Tables/Queries list again and select Table: InsurancePlans. Double-click InsuranceID and Premium. Click Next. Click by InsurancePlans. Click Next. Click Finish.

Create a new query in Design view using the following fields in this order: From the InsurancePlans table, add the InsuranceID, InsuranceType, SubscriptionType, and Premium fields. From the InsuranceProviders table, add the ProviderName field. Run the query.

On the Create tab, in the Queries group, click the Query Design button. In the Show Table dialog, double-click InsurancePlans and InsuranceProviders. Click Close. Double-click these fields in the InsurancePlans table in this order: InsuranceID, InsuranceType, SubscriptionType, and Premium. Double-click the ProviderName field in the InsuranceProviders table. On the Query Tools Design tab, in the Results group, click the Run button.

Add a new table in Design view.

On the Create tab, in the Tables group, click the Table Design button.

Create a new table in Design view. Name the first field: IncreaseID Make the field data type: AutoNumber Add the description: Unique ID Set the field as the primary key. Save the table with the Name: WageIncreases

On the Create tab, in the Tables group, click the Table Design button. Type IncreaseID Press Tab. Expand the Data Type list and select AutoNumber. Press Tab again. Type Unique ID. Click the Primary Key button. Click the Save button on the Quick Access Toolbar. Type WageIncreases in the Table Name box. Click OK.

Create a new table in Datasheet view. Save it with the name: InsurancePlans

On the Create tab, in the Tables group, click the Table button. Click the Save button on the Quick Access Toolbar. Type InsurancePlans in the Table Name box. Click OK.

Create a new relationship between the StaffID field in the Staff table and the Reviewer field in the Reviews table. Enforce data integrity between the two tables.

On the Database Tools tab, in the Relationships group, click the Relationships button. Click StaffID in the Staff table and drag to Reviewer in the Reviews table. In the Edit Relationships dialog, click the Enforce Referential Integrity check box. Click Create.

Import all of the fields from the Staff worksheet in the Staff Excel file to a new table. Use the first row as column headings. Do not change any field information. Use the StaffID field as the primary key. Accept the new table Staff. Do not save the import steps.

On the External Data tab, in the Import & Link group, click the New Data Source button, select From File, and click Excel. Click the Browse button. Click the Staff file. Click Open. Click OK. Click Next. Click the First Row Contains Column Headings check box. Click Next. Click Next. Click the Choose my own primary key radio button. Click Next. Click Finish. Click Close.

Import data from the UpdatedBenefitPlans text file and append the records to the Benefits table. The data fields in the text file are separated by tabs. The first row in the text file is the header row. Save the import steps. Use the default name suggested by Access.

On the External Data tab, in the Import & Link group, click the New Data Source button, select From File, and click Text File. Click the Browse... button. Click UpdatedBenefitPlans, and then click Open. Click the Append a copy of the records to the table: radio button, and verify that Benefits is selected. Click OK. Click Next. Click the First Row Contains Field Names check box. Click Next. Click Finish. Click the Save Import Steps check box. Click the Save Import button.

From Design view, add a Command Button control to the upper right corner of the form next to the DOB field. Use the Close Form action from the Form Operations category. Display this text on the button: Save and Close Name the button control: btnCloseForm

On the Form Design Tools Design tab, in the Controls group, click the Button button. Click in the upper right corner of the form. Click Form Operations in the Categories list. Click Close Form in the Actions list. Click Next. Click the Text: radio button. Edit the button text to Save and Close and then click Next. Name the button control: btnCloseForm

From Design view, add a list box control to the form below the InsuranceID control. The list box control should display the following three values in this order: Medical Dental Vision Store the selected value in the InsuranceType field. The label should display: InsuranceType

On the Form Design Tools Design tab, in the Controls group, expand the Controls gallery by clicking the More button. Click the List Box button. Click in the form below the InsuranceID control. Select the I will type in the values that I want. radio button. Click Next. Type Medical in the first cell under Col1 and then press Tab. Type Dental and then press Tab. Type Vision and then click Next. Click the Store that value in this field: radio button. Select InsuranceType from the list. Click Next. In the box, type InsuranceType and then click Finish.

From Design view, display the Property Sheet for this form.

On the Form Design Tools Design tab, in the Tools group, click the Property Sheet button.

Apply the Blue-Gray, Text 2 font color to the selected controls. Do not switch views.

On the Form Layout Tools Format tab, in the Font group, click the Font Color button arrow to expand the font color palette. Click Blue-Gray, Text 2 (the fourth color from the left in the top row of theme colors).

From Print Preview view, export the Staff Insurance Elections report as a Word file. Use the suggested location and file name.

On the Print Preview tab, in the Data group, click the More button. Click Word. Click OK. Click Close.

Add the Services table to the Relationships window.

On the Relationship Tools Design tab, in the Relationships group, click the Show Table button. Double-click Services. Click Close.

From Design view, add a new label control to the Page Header section above the LastName control. Change the label text to: Last Name

On the Report Design Tools Design tab, in the Controls group, click the Label control. If the Controls gallery is not visible on the Ribbon, click the Controls button to display it. Click in the report Page Header section above the LastName control. Type Last Name and press Enter.

From Design view, add a subreport to the Detail section of this report. Display all the fields from the InsurancePlans table. Accept the relationship suggested by Access, and accept the suggested name for the subreport.

On the Report Design Tools Design tab, in the Controls group, click the Subform/Subreport button. If the Controls gallery is not visible on the Ribbon, click the Controls button to display it. Click in the Detail section below the ID control. Click Next. Expand the Tables/Queries list, and select Table: Insurance Plans. Click the >> button. Click Next. Click Next. Click Finish.

From Design view, group the records in the report by values in the DepartmentName field.

On the Report Design Tools Design tab, in the Grouping & Totals group, click the Group & Sort button. Click the Add a group button in the Group, Sort, and Total pane. Click DepartmentName.

From Layout view, group this report by values in the DateOfHire field. Change the grouping to group by the year instead of by quarter.

On the Report Layout Tools Design tab, in the Grouping & Totals group, click Group & Sort. Click Add a Group. Click DateOfHire. Click the More button next to DateOfHire in the Group, Sort, and Total pane. Click the arrow next to by quarter in the group options bar. Click the by year radio button. Click outside the menu to accept the change.

From Layout view, group this report by values in the ProviderName field.

On the Report Layout Tools Design tab, in the Grouping & Totals group, click Group & Sort. Click Add a Group. Click ProviderName .

Add a new Date/Time field to the far right side of the table. Name the field: StartDate

On the Table Tools Fields tab, in the Add & Delete group, click the Date/Time button. Type StartDate and press Enter.

Set the Default Value property for the IncreaseType field to COLA.

On the Table Tools Fields tab, in the Properties group, click the Default Value button. Type COLA. Click OK.

Rename the ID field to: PlanID

On the Table Tools Fields tab, in the Properties group, click the Name & Caption button. Type PlanID in the Name box. Click OK.

Change the Field Size property for the StaffID field to 7.

On the Table Tools Fields tab, in the Properties group, type 7 in the Field Size box and press Enter. Click Yes.

Add criteria to this query to return only the records where the value in the SubscriptionType field is Family or the value in the Premium field is >200. Run the query to view the results.

Type Family in the Criteria row in the SubscriptionType column. Type >200 in the or row in the Premium column. Be careful to enter >200 in the or row not the Criteria row. On the Query Tools Design tab, in the Results group, click the Run button.

Add criteria to this query to return records where the SubscriptionType field begins with the letters Self. Run the query.

Type Like "Self*" in the Criteria row in the SubscriptionType column. On the Query Tools Design tab, in the Results group, click the Run button.

Modify this query to show only medical insurance plans. Add criteria to the InsuranceType field to return only records where the classification is Medical. Run the query.

Type Medical in the Criteria row in the InsuranceType column. On the Query Tools Design tab, in the Results group, click the Run button.