Siment Practice Test

From the current view (Layout view), add automatic totals to the Premium column to calculate the average premium. Font Size

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.

Use the Enter Field Properties dialog to rename the ID field to PlanID and change the caption to Plan ID. Do not add a description. Font Size

Click anywhere in the ID field to select it. On the Table Tools Fields tab, in the Properties group, click the Name & Caption button. Type PlanID in the Name box. Type Plan ID in the Caption box. Click OK.

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

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.

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

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

Create a filter using the Custom Filter dialog to show only records where the value in the InsuranceProvider field contains the word Care. Font Size

Click the arrow at the top of the InsuranceProvider column. Point to Text Filters, and select Contains... Type Care in the InsuranceProvider contains box. Click OK.

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

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.

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

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.

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

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.

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

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

Create a Split form from the InsurancePlans table. Font Size

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

From the current view (Datasheet view), set the Default Value property for the IncreaseType field to COLA. Font Size

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

From the current view, rename the ID field to: PlanID Font Size

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

Unhide the DOB field. Font Size

Right-click any column header and select Unhide Fields. In the Unhide Columns dialog, click the check box next to DOB so a checkmark appears in the box. Click Close.

Hide the DOB field. Font Size

Right-click the DOB column header and select Hide Fields.

Go to record number 14. Font Size

Type 14 in the Current Record box at the bottom of the table. Press Enter.

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

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.

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

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

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

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

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

Set the sort order so the results will display records with the highest Premium first. Run the query and review the results. Font Size

Click in the Sort row under the Premium field. Click the arrow to expand the sort options list, and select Descending. On the Query Tools Design tab, in the Results group, click the Run button.

From the current view (Layout view), move the Premium column to place it between the DOB and ProviderName columns in this report. Font Size

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.

Use Filter by Selection to filter the table to show only records where the value of the IncreaseDate field is on or after 12/31/2019. Font Size

Click in in the IncreaseDate field in a record where the value is 12/31/2019. On the Home tab, in the Sort & Filter group, click the Selection button and select On or After 12/31/2019.

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

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

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.

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

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.

Create a new database from the Time card template. Do not change the location. Name the database: Time Sheets. Font Size

Click the File tab. Click New. Click the Time Card icon. Type Time Sheets in the File Name box. Click the Create 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. Font Size

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.

Go to the last record. Font Size

Click the Last Record button at the bottom of the table.

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

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

From the current view (Layout view), move the Premium label control and its bound control to the empty layout area directly to the right of the InsuranceProvider controls in this form. Font Size

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.

Sort the records in this table so they are first grouped by the value in the DateOfHire field from newest to oldest and then alphabetically from A-Z by the value in the JobTitle field. Font Size

Click the arrow at the top of the JobTitle column and select Sort A-Z. Click the arrow at the top of the DateOfHire column and select Sort Newest to Oldest. Don't forget that when sorting by multiple fields, start with the innermost sort and work your way out.

Use AutoFilter to filter the query results first to show only records where value in the StaffID field is Mo and the value in the IncreaseType field is Merit. Font Size

Click the arrow at the top of the StaffID column and click the (Select All) check box to remove all the checkmarks. Click the Mo check box to add a checkmark. Click OK. Click the arrow at the top of the IncreaseType column and click the (Select All) check box to remove all the checkmarks. Click the Merit check box to add a checkmark. Click OK.

Use AutoFilter to filter the query results to show only records where the value of the StaffID field is Mo or Shannon. Font Size

Click the arrow at the top of the StaffID column and click the (Select All) check box to remove all the checkmarks. Click the Mo check box to add a checkmark. Click the Shannon check box to add a checkmark. Click OK.

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

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

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

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.

Change the Navigation Pane grouping option to Tables and Related Views. Font Size

Click the top of the Navigation Pane to display the category and group list. In the Navigate to Category section, select Tables and Related Views.

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

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

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 Font Size

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, modify this form's properties to not allow new records. Font Size

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.

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

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 Next. Click Finish.

Start a new query in Design view. Font Size

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

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

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.

Create a new blank report that will open in Layout view. Font Size

On the Create tab, in the Reports group, click the Blank Report 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 Font Size

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 form based on the List form application part. Font Size

On the Create tab, in the Templates group, click the Application Parts button. In the Blank Forms section, click List.

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

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

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 properties to display the message Excessive overtime is not allowed if the table validation rule is violated. Font Size

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.

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

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

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

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

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

From the current view (Layout view), use the Field List pane to add the Premium field from the InsurancePlans table to the form to the right of the InsuranceID controls. Font Size

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 Insurance Plans. Click and drag Premium from the list of fields under InsurancePlans to the right of the InsuranceID controls in the form.

From the current view (Layout view), use the Field List pane to add the InsuranceID field from the InsurancePlans table to the form. Font Size

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.

Replace all instances of the first name jody with Jody. Be sure to match the case exactly. Font Size

On the Home tab, in the Find group, click the Replace button to open the Find and Replace dialog. Type jody in the Find What box. Type Jody in the Replace with box. Click the Match Case check box. Click Replace All. Click Yes.

Display the Total row in this table and calculate the sum of the values in the OvertimeHours field. Font Size

On the Home tab, in the Records group, click the Totals button. Click in the OvertimeHours column in the Total row. Click the arrow to expand the list of functions and select Sum.

Sort the records in this table by the values in the DateOfHire field, so records with the oldest dates appear first. Font Size

On the Home tab, in the Sort & Filter group, click the Ascending button.

Clear the filter from the StaffID field. Font Size

On the Home tab, in the Sort & Filter group, click the Toggle Filter button.

Display the Total row for this query. Font Size

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

Modify this query so results are grouped by values in the InsuranceProvider field and values in the Premium field are summarized with the Sum function. Run the query. Font Size

On the Query Tools Design tab, in the Show/Hide group, click the Totals button. In the Total row, under Premium, expand the Group by list and select Sum. On the Query Tools Design tab, in the Results group, click the Run button.

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

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 the current view (Layout view), group this report by values in the ProviderName field. Font Size

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

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

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.

From the current view (Layout view), 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. Font Size

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.

From the current view, add a new Date/Time field to the far right side of the table. Name the field: StartDate Font Size

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

Add the Name Quick Start fields to the end of the table. Font Size

On the Table Tools Fields tab, in the Add & Delete group, click the More Fields button. Scroll to the bottom of the Field Types gallery to the Quick Start section. Click Name.

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

Type >250 in the Criteria row in the Premium 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 Premium field is greater than 200 and less than 250. Run the query to view the results. Font Size

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.

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

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.