CIS 2

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

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.

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.

Display the Total row for this query.

On the Query Tools Design tab, in the Show/Hide group, click the Totals 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.

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.

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.

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

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

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.

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.

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.

Start a new query in Design view.

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.

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.

Use the Simple Query Wizard to create a select query for a single table. Include these fields in this order: LastName, FirstName, and DOB fields from the Staff table. Select the option to open the query to view information. Name the query Birthdays and view the query results.

On the Create tab, in the Queries group, click the Query Wizard button. In the New Query dialog, Simple Query Wizard is selected by default. Click OK. Expand the Tables/Queries list and select Table: Staff. Double-click each of the field names in this order: LastName, FirstName, and DOB. Click Next. Type Birthdays in the What title do you want for your query? box. Click Finish.

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.

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.

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

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.