CIS

Create a relationship between the EquipmentID primary key and the FKEquipmentID foreign key. Enforce referential integrity in the relationship.

Click the EquipmentID field and drag it over the FKEquipmentID field and release the mouse. Click the Enforce Referential Integrity check box. Click Create.

Change the EquipmentID Field Size to 5.

Click the Field Size property box for the EquipmentID field. Type 5 in the Field Size property box and press Enter.

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.

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.

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.

Insert a new row into the form layout above the InsuranceType controls.

Click anywhere in the row. On the Form Layout Tools Arrange tab, in the Rows & Columns group, click the Insert Above button.

In Design view, delete the Comments field.

Click the Comments row selector. Select the Table Tools Design tab. Click the Delete Rows button. Click Yes.

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 .

Delete the relationship in this database.

Select the Database Tools tab. Click the Relationships button. Select the relationship line. Right-click the relationship line between the Equipment table and the OrderLineDetails field. Select Delete. Click Yes to confirm the deletion. Click Close.

Add the Equipment and ServiceHistory tables to the Relationships tab.

Select the Equipment. Click Add. Select the ServiceHistory table and click Add. Click Close.

From Design view, add a field validation rule to the RegularHours field to require that values are less than or equal to 40.

Type <=40 in the Validation Rule box in the Field Properties pane. Press Enter.

Add the Validation Rule Like "#####" to the EquipmentID field.

Click the Validation Rule property box. Type Like "#####" in the Validation Rule property box.

From Design view, modify the form's property to restrict data entry to new records only.

On the Form Design Tools Design tab, in the Tools group, click the Property Sheet button. Click in the Data Entry property box, expand the list, and select Yes.

Expand the Navigation Pane, and open the Staff Form form.

Click the Shutter Bar Open/Close button. Double-click Staff Form in the Forms section of the Navigation Pane.

Set the default value for the State field to CA.

Click the State row selector. Click the Default Value Field Property. ype CA and press Enter.

Close Print Preview without closing the table.

On the Print Preview tab, in the Close Preview group, click the Close Print Preview button.

Open the Relationship Tools Design tab and the Show Table window.

Click the Database Tools tab. Click the Relationships button.

Open the Print Preview tab for the Equipment table.

Click the Equipment table on the Navigation Pane. Click the File tab. Click the Print button. Click Print Preview.

Add a new field named ProductName that is the Short Text data type without leaving Design view.

Click the first blank field name cell to select it. Type ProductName and press Enter to move to the Data Type cell. Click the Data Type drop down arrow. Select Short Text.

From Design view, apply the Currency format to the IncreaseAmount field. Change only the formatting, not the data type.

In the Field Properties pane, click in the Format property box. Click the arrow at the right end of the box to expand the list of available formats, and select Currency.

Create a new blank form that will open in Layout view.

On the Create tab, in the Forms group, click the Blank Form 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.

Modify the table properties to display the message Excessive overtime is not allowed if the table validation rule is violated.

On the Design tab, in the Show/Hide group, click the Property Sheet button. In the Validation Text box, type message Excessive overtime is not allowed and then press Enter.

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 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.

Move the Premium column to place it between the DOB and ProviderName columns.

Click anywhere in the Premium column. On the Report Layout Tools Arrange tab, in the Rows & Columns group, click the Select Column button. When the pointer changes to the move shape, click and drag until the I-bar shape appears between the DOB and ProviderName columns and then release the mouse button.

Using the current view, delete the Attachments field from the table.

Click in the Attachments field. On the Table Tools Design tab, in the Tools group, click the Delete Rows button. Click Yes.

Create a new table in Design view.

Click the Create tab. Click the Table Design button.

Add a Lookup Wizard to the Category field with the options Generators; Hand Tools; Plumbing; Sawing, Cutting; and Tractors.

Click the Data Type property field. Click the drop-down arrow. Select Lookup Wizard. Select the I will type in the values that I want radio button. Click the Next button. Click the first cell in the Col1 column. Type Generators in cell 1, Hand Tools in cell 2, Plumbing in cell 3 , Sawing, Cutting in row 4, and Tractors in row 5. Click the Next button. Click the Limit To List check box. Click the Finish button.

Edit the relationship in this database to Cascade Update Related Fields. Close the relationship view when you are finished.

Click the Database Tools tab. Click the Relationships button. Select the relationship line. Click Edit Relationships. Select the Cascade Update Related Fields check box. Click OK. Click Close.

Change the Format property for the DatePurchased field to Medium Date.

Click the DatePurchased row selector. Click the Format property. Click the Format property drop-down arrow. Select Medium Date.

Add the Medium Date Input Mask to the DatePurchased field. Do not edit the input mask or change the placeholder character.

Click the DatePurchased row selector. Click the Input Mask property box. Click the Build button. Select the Medium Date option. Click Next. Click Next. Click Finish.

Preview how the results of this query will look when printed.

Click the File tab to open Backstage. Click Print. Click the Print Preview button.

Preview how the results of this query will look when printed when the page orientation is changed to landscape.

Click the File tab to open Backstage. Click Print. Click the Print Preview button. On the Print Preview tab, in the Page Layout group, click the Landscape button.

Run the Compact & Repair tool to optimize database efficiency.

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

Compact & Repair the open database.

Click the File tab. Click the Compact & Repair Database button.

Apply the Zip Code input mask format to the selected field (Zip/Postal Code). Do not change default format or placeholder characters. Store the data without the symbols.

Click the Input Mask box in the Field Properties pane. Click the Build... button. Click Zip Code. Click Next. Click Next. Click Next. Click Finish.

Set the Margins to Normal.

Click the Margins button [Print Preview tab, Page Size group]. Select Normal.

Move the Premium label control and its bound control to the empty layout area directly to the right of the InsuranceProvider controls.

Click the Premium label control. Press Ctrl and click the bound control displaying the premium value. Click and drag to move both controls down to the empty layout area to the right of the InsuranceProvider controls.

Create this record validation rule: [WeeklyRentalRate]>[DailyRentalRate] And [MonthlyRentalRate]>[WeeklyRentalRate]

Click the Table Tools Design tab. Click the Property Sheet button. Click the Validation Rule property on the Property Sheet pane. Type [WeeklyRentalRate]>[DailyRentalRate] And [MonthlyRentalRate]>[WeeklyRentalRate]. Press Enter.

Use the Expression Builder to create the validation rule Like "#####".

Click the Validation Rule property box. Click the Build button. Type Like "#####". Click OK.

Add Validation Text, The Equipment ID must contain 5 numbers for the EquipmentID field.

Click the Validation Text property box. Type The Equipment ID must contain 5 numbers. Press Enter.

Switch to Datasheet view.

Click the View button.

Switch to Design view.

Click the View button.

From Layout view, create a new conditional formatting rule for the selected field. If the field value is less than or equal to the value in the RequiredHours field, apply bold, red formatting. Red is the second color from the left in the last row of the color palette.

Click the in the report under the QuantityInStock label. Do not click the label itself. On the Report Layout Tools Format tab, in the Control Formatting group, click the Conditional Formatting button to open the Conditional Formatting Rules Manager. Click the New Rule button. In the New Formatting Rule dialog, in the Format only cells where the: section, expand the middle box and select less than or equal to. In the next box type: [RequiredHours] Click the Bold button. Click the Font Color button to expand the color palette and click Red (the second color from the left in the last row). Click OK. Click OK again.

In Design view change the primary key to the ProductName field.

Click the row selector for the ProductName field. Click the Table Tools Design tab. Click the Primary Key button.

In Design view, add a new field before the DailyRentalRate field.

Click the row selector next to the DailyRentalRate field. Click the Table Tools Design tab. Click the Insert Rows button.

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.

Create a Single Record form from the InsurancePlans table

On the Create tab, in the Forms group, click the Form button.

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.

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.

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.

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

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 Update Related Fields check box. Click OK.

Enter a validation rule for the table to require values in the OvertimeHours field to be less than the values in the RegularHours field. Remember to enclose the field names in square brackets.

On the Design tab, in the Show/Hide group, click the Property Sheet button. Type [OvertimeHours]<[RegularHours] in the Validation Rule box. Press Enter.

From Design view, add a Command Button control to the form.

On the Form Design Tools Design tab, in the Controls group, click the Button button. Click in the details section of the form.

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.

From Design view, modify this form's properties to not allow new records.

On the Form Design Tools Design tab, in the Tools group, click the Property Sheet button. Click in the Allow Additions box, expand the list, and select No.

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.

Add a title to the form header and change the text to: Insurance Benefit Press Enter when you are finished. Do not switch views.

On the Form Layout Tools Design tab, in the Header/Footer group, click the Title button. Type Insurance Benefit and then press Enter.

Use the Field List pane to add the Premium field to the form to the right of the InsuranceID controls. Do not switch views.

On the Form Layout Tools Design tab, in the Tools group, click the Add Existing Fields button. In the Field List pane, click and drag Premium from the list of fields under InsurancePlans to the right of the InsuranceID controls in the form.

Use the Field List pane to add the InsuranceID field from the InsurancePlans table to the form. Do not switch views.

On the Form Layout Tools Design tab, in the Tools group, click the Add Existing Fields button. In the Field List pane, click the + in front of InsurancePlans. Double-click InsuranceID.

Change the orientation to Landscape.

On the Print Preview tab, in the Page Layout group, click the Landscape button.

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, add the date and time to the report header using the date format similar to Saturday, March 02, 2016 and the time format similar to 1:12 PM.

On the Report Layout Tools Design tab, in the Header/Footer group, click the Date and Time button. In the Date and Time dialog, click the radio button with the time format similar to 1:12 PM. Do not change any other options in the dialog. Click OK.

Add automatic page numbers centered at the bottom of the page footer on every page. Use the format page N of M. Do not change views.

On the Report Layout Tools Design tab, in the Header/Footer group, click the Page Numbers button. In the Page Numbers dialog, click the Page N of M radio button. Click the Bottom of Page (Footer) radio button. Click OK.

Use the Field List pane to add the following fields to the report: From the Staff table, add the LastName and then FirstName fields. From the InsurancePlans table, add the SubscriptionType and Premium fields. Do not switch views.

On the Report Layout Tools Design tab, in the Tools group, click the Add Existing Fields button. In the Field List pane, click the + in front of Staff. Double-click LastName and then double-click FirstName. Click the + in front of InsurancePlans. Double-click SubscriptionType and then double-click Premium.

Add a new conditional formatting to the selected field to display data bars. Use the default data bars settings.

On the Report Layout Tools Format tab, in the Control Formatting group, click the Conditional Formatting button to open the Conditional Formatting Rules Manager. Click the New Rule button. In the New Formatting Rule dialog, in the Select a rule type: list, click Compare to other records. Click OK. Click OK again to close the Conditional Formatting Manager.

From Layout view, delete the conditional formatting rule for the selected field.

On the Report Layout Tools Format tab, in the Control Formatting group, click the Conditional Formatting button to open the Conditional Formatting Rules Manager. Ensure the Value <= [RequiredHours] rule is selected and click the Delete Rule button. Click OK.

Using the current view, insert a new Short Text field named FirstName between the StartDate and LastName fields. Save the table when you are finished.

On the Table Tools Design tab, in the Tools group, click the Insert Rows button. Type FirstName in the FieldName cell and press Tab. Expand the Data Type list and select Short Text. Click the Save button on the Quick Access Toolbar.

From Design view, modify the field properties to display the message Regular hours cannot be greater than 40 when the field validation rule is violated.

Type Regular hours cannot be greater than 40 in the Validation Text box in the Field Properties pane. Press Enter.