Assignment #2

Access Chapter 2 Apply Your Knowledge Page AC 125

Reinforce the skills and apply the concepts you learned in this chapter.

Note: Make sure you finished Assigment #1.

Using Wildcards in a Query, Creating a Parameter Query, Joining Tables, and Creating a Report

Instructions: Start Access. Open the Apply Cosmetics Naturally database that you modified in Assignment #1.

Put your initials before the names of all the database objects you are going to add to the database! For Assignments, you work on Apply Cosmetics Naturally database!

Read the Note and Instructions on page AC 125, then perform Task1Task5.

Dont print out your file. I will check them online during the class time on February 5.

Perform the following tasks (Task1Task5):

Create a query for the Customer table and add the Customer Number, Customer Name, Amount Paid, and Balance fields to the design grid. Add a criterion to find all customers whose names start with the letter C. Run the query and then save it as Apply 2 Step 1 Query.

Create a query for the Customer table and add the Customer Number, Customer Name, Amount Paid, and Sales Rep Number fields to the design grid. Sort the records in descending order by Amount Paid. Add a criterion for the Sales Rep Number field that allows the user to enter a different sales rep each time the query is run. Run the query and enter 41 as the sales rep number to test the query. Save the query as Apply 2 Step 2 Query.

Create a query that joins the Sales Rep and Customer tables. Add the Sales Rep Number, Last Name, and First Name fields from the Sales Rep table and the Customer Number and Customer Name fields from the Customer table to the design grid. Sort the records in ascending order by Customer Number within Sales Rep Number. All sales reps should appear in the result, even if they currently have no customers. Run the query and save it as Rep-Customer Query.

Create the report shown in Figure 288. The report uses the Rep-Customer Query.

Rename the Rep-Customer Report in the Navigation Pane as LastName-Customer Report where LastName is your last name.

Figure 288

Write down the SQL statements of Task1Task3 here:

(In the simplest format, that is, if a field only from one table, do not write the table name, and also remove unnecessary brackets and parentheses)

FLApply2Step1Query

FLApply2Step2Query

FLRep-CustomerQuery