CO SCI Simnet Project Access Ch 4 Fix It 4.6

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

Fix It 4.6 In this project, you will be working with the pet store database from Chapter 3, Fix It 3.6. The store is currently having trouble with some queries. Find and fix its problems. Be sure to save the database objects after you make changes.

Skills needed to complete this project:

• Using a Parameter Query • Adding a Calculated Field to a Query • Hiding and Showing Fields in a Query • Adding Numeric and Date Criteria to a Query • Specifying the Sort Order in a Query • Adding Text Criteria to a Query • Using AND in a Query • Using OR in a Query • Combining AND and OR in a Query • Importing Data from Excel

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.

Step 1 Download start file

1. Open the start file AC2016-FixIt-4-6.

2. If the database opens in Protected View, click the Enable Editing button in the Message Bar at the top of

the database so you can modify it.

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

do so by your instructor, and save it.

4. The CustomersByPhone query is not working properly. Fix it so when this query is run, it prompts the user to enter a phone number in order to find a particular customer. Use the prompt: Enter

customer phone number

5. Fix the FeeIncrease query to correctly display a calculated field that increases the pet adoption fees by 10

percent and hides the original adoption fee. Change the name of the calculated field to: NewFee

6. The OlderDogAdoptions query has several problems. Fix it so it correctly displays only dogs that are at

least four months of age. Sort the results by pet age from the youngest to the oldest.

7. Fix the Customers-W&B query so only customers whose last names begin with a W or a B appear in the

query results.

8. A previous volunteer kept the employee list in an Excel spreadsheet. Import the data from the Staff

Excel spreadsheet into a new table named: Staff Download Resources

1 | Page Fix It 4.6 Last Updated 10/31/17

The Excel file is located with the data files for this project.

a. The first row of the Excel file contains column headings.

b. The HourlyRate field should be imported using the Currency data type.

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

c. The table should use the StaffID field as the primary key.

d. The imported table should be named: Staff

Step 2 Upload & Save

Step 3 Grade my Project

9. Close the database and exit Access.

10. Upload and save the project file.

11. Submit project for grading.

2 | Page Fix It 4.6 Last Updated 10/31/17