BUSI 201 Quiz 4 Access solutions complete answers

BUSI 201 Quiz 4 Access solutions complete answers 

 

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

 

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.

 

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

 

Add a new record to this form.

 

Add a new table in Design view.

 

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.

 

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 InsuranceType field is Dental or Vision. Run the query to view the results.

 

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.

 

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

 

Add the Services table to the Relationships window.

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

Create a Multiple Items form from the InsurancePlans table.

 

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.

 

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.

 

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

 

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

 

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.

 

Create a Split form from the InsurancePlans table.

 

Delete the Copy of Staff table.

 

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

 

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.

 

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.

 

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.

 

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.

 

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.

 

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

 

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

 

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

 

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

 

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

 

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

 

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.

 

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.

 

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.

 

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

 

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.

 

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.

 

Rename the ID field to: PlanID

 

Run the Compact & Repair tool to optimize database efficiency.

 

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

 

Share the document with kendishner@hhspa.com and include the message Please proofread and edit. The recipient should be able to edit the document.

 

Start a new query in Design view.

 

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.

 

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.

 

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

 

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.

 

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.

 

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.

 

Display the Total row for this query.

 

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

 

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

 

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

 

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

 

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.

 

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.

 

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

 

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

 

Zip the contents of the Finances folder.

 

From Design view, add a field validation rule to the AccountID field to require that values are less than 2000.

 

From the current view (Design view), apply the Medium Time format to the StartTime field.

 

From Design view, modify the field properties to display the message Asset accounts must be in the 1000s when the field validation rule is violated.