2019 Access Test

Go to the previous record.

At the bottom of the query, click the previous record button.

From the current view, move the StartDate field so it is the last field in the table.

Click and hold down the StartDate to move it.

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

Click create tab, in the forms group, click blank form

Create a Split form from the InsurancePlans table.

Click create tab, in the forms group, click more forms, then click split form.

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

Click create tab. Then click blank report (reports).

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.

Click on the date 12/31/2019 in the IncreaseDate column. In the sort & filter group click on or after on the selection drop down box.

Apply the zip code input mask format to the selected field (Zip/postal code). Do you know 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 sort order so the results will display records with the highest Premium first. Run the query and review the results.

Click 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, and the results group, click the run button.

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

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

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.

Click the arrow at the top of the job title column and select store A-to-Z. Click the arrow at the top of the date of hire column and select sort newest to oldest. Don't forget that one sorting by multiple fields start with the enormous sort and work your way out.

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

Click the attachments field. On the table total design tab, in the tools group, click the delete rows button. Click yes.

From the current view (Datasheet view), set the Default Valueproperty for the IncreaseType field to COLA.

Click the increasetype column. Click the table tools field tab. And properties click the default value. Type COLA into the increasetype field.

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

Modify the width of the ServiceName column to best fit the data.

Double click in between row that says service name and price until it expands.

Open the Services table.

Double click on the Services table in the All Access Objects Pane.

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 on the Format property box. Click the arrow at the right end of the box to expand the list of available formats, and select Currency.

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

In the query, in criteria row, premium column put >250. Click design tab, click run.

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.

In the query, under premium field, in the criteria row, type >250 and <250

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.

In the query. On the drop-down list for StaffID, clear all checkmarks, check MO. On the drop-down list for increasetype, clear all checkmarks, check merit.

Use the Search box to begin searching for a student with the last name beginning with Pat.

In the search box at the bottom of the query type in Pat.

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 department name, expand the group by list and select where. In the criteria row under department name, type management. On the query tools design tab, and the results group, click the run 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.

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.

On the Form Layout 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.

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

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.

Clear the filter from the StaffID field.

On the StaffID, click the drop-down button. Click clear filter.

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, and the queries group, click the query design button. In the 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 and the insuranceproviders table. On the query tools design tab, and the results group, click the run button.

Create a new form based on the List form application part.

On the create tab, in the templates group, click the application parts button. In the blank form 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.

On the database tools tab, and the relationships group, click the relationships button. Click staff ID in the staff table and drag to review were 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 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, 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.

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

On the home tab, in the records group, click the drop-down button on delete. Click delete record. Click yes.

Switch this report to Design view.

On the home tab. Click the view button drop down and select design view.

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.

On the query tools design tab, and the show/hide group, click the totals button. And the total row, under premium, expand the group by list and select sum. On the query tools design tab, and 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.

On the report layout tools design tab, and the grouping and totals group, click group and 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 you by quarter in the group options bar. Click the buy your radio button. Click outside the menu to except the change.

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.

On the report layout tools design tab, and the tools group, click the add assisting fields button. In the Fields list pane, click the + in front of staff. Double click last name and then double click first name. Click the + in front of insurance plans. Double click subscription type and then double click premium.

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.

Quick report layout tools design tab, then page numbers ( header/ footer). Under format click page N of M. Under position click bottom of page [footer]. Click okay.

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

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 self in the criteria row in the subscriptiontype column. Take Family in the or row in the subscriptiontype column. Type <200 in the criteria row in the premium column. Type <200 in the or row in the Premium column. On the Query Tools Design tab, in the results group, click the run button.

Change the Navigation Pane grouping options to Tables and Related Views.

right-clicked the Navigation Pane Header, clicked the Navigation Pane Header. In the Category/Group menu, you clicked the Tables and Related Views menu item.