A Skills Approach: Access 2016 Chapter 4: Using Queries and

Organizing Information

1 | Page Skill Review 4.2 Last Updated 9/18/17

Skill Review 4.2 In this project you will continue working with the health insurance database from Chapter 3, Skill Reivew 3.2. You will improve the functionality of the database by creating a variety of queries. You will also import data from a text file and link to a table in another Access database.

Skills needed to complete this project:

• Using the Simple Query Wizard

• Adding Numeric and Date Criteria to a Query

• Using OR in a Query

• Using AND in a Query

• Creating a Query in Design View

• Adding Text Criteria to a Query

• Hiding and Showing Fields in a Query

• Adding a Calculated Field to a Query

• Finding Unmatched Data Using a Query

• Finding Duplicate Data Using a Query

• Using a Parameter Query

• Importing Data from a Text File

• Linking to a Table in Another Access Database

IMPORTANT: Download the resource file needed for this project from the Resources link. Be sure to extract the file after downloading the resources zipped folder. Please visit SIMnet Instant Help for step-by-step instructions.

1. Open the start file AC2016-SkillReview-4-2.

2. If necessary, enable active content by clicking the Enable Content button in the Message Bar.

3. The file will be renamed automatically to include your name. Change the project file name if directed to do

so by your instructor.

4. Use the Query Wizard to create a select query from the Physicians table.

a. On the Create tab, in the Queries group, click the Query Wizard button. In the New Query dialog, verify

that Simple Query Wizard is selected. Click OK.

b. Click the Tables/Queries drop-down arrow. Select Table: Physicians. Click the >> button to add

all the fields to the Selected Fields list. In the Selected Fields list, click the PhysicianID field and then click

the < button to remove the PhysicianID field from the list. Click Next.

c. In this step, verify that Detail is selected and click Next.

d. In the last step, type PhysiciansByZipCode for the title. Select the radio button to Modify the

query design and click Finish.

5. Add criteria to the query to limit the query to physicians with the zip code 33176 or 33186.

a. Type 33176 in the Criteria row under the ZipCode field.

b. Below that, in the or row, type: 33186

c. Click the drop-down arrow in the Sort row under the LastName field. Select Ascending.

d. Click the Run button and review the query results.

e. Save and close the query.

Step 1

Download start file

 

 

A Skills Approach: Access 2016 Chapter 4: Using Queries and

Organizing Information

2 | Page Skill Review 4.2 Last Updated 9/18/17

6. Create a query in Design view:

a. On the Create tab, in the Queries group, click the Query Design button. In the Show Table dialog, double-

click the Procedures table. Click the Close button.

b. Notice the Procedures table in the upper pane of the query Design view window. Double-click each field

name in the field list except ProcedureID in order to add them to your query.

c. Type Yes in the Criteria row under the Covered field.

d. Type <=40 in the Criteria row under the ReimbursementAmt field.

e. Uncheck the Show box under the Covered field.

f. Click the Run button to check your work and then return to Design view.

7. Add a calculated field to the query to display the result of a 7% increase in the reimbursement amount.

a. Next to the ReimbursementAmt field, create a new calculated field by typing the following in the Field row:

7% Increase:[ReimbursementAmt]*1.07

Hint: If you need more room to type the expression, press Shift + F2 key to open the Zoom window.

b. Click the Run button to check your work and then return to Design view.

8. Add the OrderDate field from the Orders table to the query.

a. On the Query Tools Design tab, in the Query Setup group, click the Show Table button. Double-click the

Orders table and then click the Close button.

b. Double-click the OrderDate field in the Orders table to add it to the query.

c. Click the Run button and observe the new query results.

d. Save the query with the name: CheapCoveredProcedures

e. Close the query.

9. Use the Unmatched Query Wizard to find records in the Procedures table without corresponding records in the

Orders table.

a. On the Create tab, in the Queries group, click the Query Wizard button. In the New Query dialog, click

Find Unmatched Query Wizard and click OK.

b. Select Table: Procedures. Click Next.

c. Select Table: Orders. Click Next.

d. Confirm that Access has matched the ProcedureID field in the Procedures table and the Procedure field

in the Orders table, and then click Next.

e. Add all of the fields to the query by clicking the >> button. Click Next.

f. Change the name to ProceduresNotOrdered and click Finish.

g. Observe the query results and then close the query.

 

 

A Skills Approach: Access 2016 Chapter 4: Using Queries and

Organizing Information

3 | Page Skill Review 4.2 Last Updated 9/18/17

10. Use the Find Duplicates Query Wizard to find records in the Orders table with the same values in the Physician

and OrderDate fields.

a. On the Create tab, in the Queries group, click the Query Wizard button. In the New Query dialog, click

Find Duplicates Query Wizard and click OK.

b. Select Table: Orders. Click Next.

c. Select OrderingPhysician and add it to the right side by clicking the > button. Do the same for

OrderDate. Click Next.

d. Add all fields by clicking the >> button. Click Next.

e. Change the name to SameDateOrders and click Finish.

f. Observe the query results and then close the query.

11. Create a parameter query to allow database users to find records in the Physicians table by entering a

city name.

a. On the Create tab, in the Queries group, click the Query Design button. In the Show Table dialog, double-

click the Physicians table. Click the Close button.

b. Notice the Physicians table in the upper pane of the query Design view window. Double-click the

following field names in order to add them to your query: FirstName, LastName, City, Phone

c. Click the Criteria row under the City field and type the following: [Enter the city name]

d. Click the Run button. In the Enter Parameter Value dialog, type: Miami and then click OK. Observe the

query results.

e. Save the query with the name: PhysiciansByCity

f. Close the query.

12. Import data from the EmployeeTransfers tab-delimited text file and append the records to the Physicians table.

a. On the External Data tab, in the Import & Link group, click the Text File button.

b. In the Get External Data – Text File dialog, click the Browse… button.

c. Navigate to the folder where the resource files for this project are stored and double-click the

EmployeeTransfers text file.

d. Click the Append a copy of the records to the table: radio button and select Physicians. Click OK.

e. Verify that the Delimited radio button is selected. Click Next.

f. Verify that the Tab radio button is selected. Click the First Row Contains Field Names check box.

Click Next.

g. Verify that Physicians is listed in the Import to Table box. Click Finish.

h. Click Close.

Download Resources

 

 

A Skills Approach: Access 2016 Chapter 4: Using Queries and

Organizing Information

4 | Page Skill Review 4.2 Last Updated 9/18/17

13. Import the HealthPlan and DentalPlan tables in the InsurancePlans database as linked tables.

a. On the External Data tab, in the Import & Link group, click the Access button.

b. In the Get External Data – Access Database dialog, click the Browse… button.

c. Navigate to the folder where the resource files for this project are stored and double-click the

InsurancePlans Access file.

d. Click the Link to the data source by creating a linked table. radio button. Click OK.

e. In the Link Tables dialog, click HealthPlan and DentalPlan.

f. Click OK.

14. Close the database and exit Access.

15. Upload and save the project file.

16. Submit project for grading.

Step 2

Upload & Save

Step 3

Grade my Project

Download Resources