In this exercise, you will learn how to create a query based on data that a user
Question:
In this exercise, you will learn how to create a query based on data that a user enters and how to use that query to create a data entry form.
a. Download the Microsoft Access file Ex05.
Open the file and familiarize yourself with the data in the Customer table.
b. Click Create in the Access ribbon. On the far right, select Query Design. Select the Customer table as the basis for the query. Drag Customer Name, Customer Email, Date Of Last Rental, Bike Last Rented, Total Number Of Rentals, and Total Rental Revenue into the columns of the query results pane (the table at the bottom of the query design window).
c. In the CustomerName column, in the row labeled Criteria, place the following text:
[Enter Name of Customer:]
Type this exactly as shown, including the square brackets. This notation tells Access to ask you for a customer name to query.
d. In the ribbon, click the red exclamation mark labeled Run. Access will display a dialog box with the text “Enter Name of Customer:” (the text you entered in the query Criteria row).
Enter the value Scott, Rex and click OK.
e. Save your query with the name Parameter Query.
f. Click the Home tab on the ribbon and click the Design View (upper left-hand button on the Home ribbon). Replace the text in the Criteria column of the CustomerName column with the following text. Type it exactly as shown:
Like “*” & [Enter part of Customer Name to search by:] & “*”
g. Run the query by clicking Run in the ribbon.
Enter Scott when prompted Enter part of Customer Name to search by. Notice that the two customers who have the name Scott are displayed. If you have any problems, ensure that you have typed the phrase previous shown exactly as shown into the Criteria row of the CustomerName column of your query.
h. Save your query again under the name Parameter Query. Close the query window.
i. Click Create in the Access ribbon. Under the Forms group, select the down arrow to the right of More Forms. Choose Form Wizard. In the dialog that opens, in the Tables/Queries box, click the down arrow. Select Parameter Query. Click the double chevron (>>) symbol and all of the columns in the query will move to the Selected Fields area.
j. Click Next three times. In the box under What title do you want for your form? enter Customer Query Form and click Finish.
k. Enter Scott in the dialog box that appears.
Access will open a form with the values for Scott, Rex. At the bottom of the form, click the right-facing arrow and the data for Scott, Bryan will appear.
l. Close the form. Select Object Type and Forms in the Access Navigation Pane. Double-click Customer Query Form and enter the value James. Access will display data for all six-customers having the value James in their name.
Step by Step Answer: