Assignment: Access Ch12 – Queries II
Files necessary for assignment: Ch_12_DizzyDonuts.accdb
Special Instructions:
After the final step of this activity, your book says that you will turn in three queries. You will, in
fact, turn in 4 queries:
(1) CustOrderParamQry, (2) ExpensiveProdParamQry, (3) ProdPriceQry, and (4)
ProdPriceRoundUpQry
Objective
In this activity, you will learn how to use parameters to simplify entering query selection criteria.
When you needed Adam’s orders from DizzyDonuts, you entered his name in the query design
grid. That is not ideal for casual users. This activity will show you how using query parameters
avoids this step.
Chapter Connection
People who work with customer information must often specify whose information they need
to review. Queries can do that, but a customer service agent should not have to modify the
query design for each question!
Activity:
In this activity, you will provide a simple interface to enter data for a query. For example, a
database user could ask for information on sales between June 1 and August 31 one time,
September 1–30 another, without needing to alter the query itself.
1. Download and open the Ch 12 DizzyDonuts. This is a fresh copy of the database you have
already used.
2. The first query you developed there was to find out how often a given customer uses the
mobile ordering service. This one will be similar. Create a new Query Design with Customer,
Order,and Store tables. Enter CustName, StoreCity, OrderDate, and OrderTotal in the grid.
3. In the Chapter 10 activity, you entered “Adam” as the CustName selection criterion. Here,
we will use a parameter instead. Each time the query is run, Access will prompt the user to
enter a value. No fixed value, Adam or anything else, is built into the query. To enter a
parameter, instead of entering the desired value in the Criteria section of the grid, enter a
prompt string there between square brackets, like this
Into which you can enter a customer name. The text above the data entry box is between the
brackets in the query design grid. Enter “Adam,” as shown, and click “OK.” You should see a list
of all Adam’s orders, with cities, dates, and totals.
Usage Hint: There are a few rules about what you put in a prompt string. It must fit on one line
(about 40 characters), it cannot be a field name, and it cannot use “.” (period), “!” (exclamation
point), “” (ampersand), “[” or “]” (square brackets).
If your query uses multiple criteria, you can have parameters in several of them. Here, you
could look for all the orders from a specific customer (one parameter) after a specific date (a
second parameter) at a specific store (a third one)
4. To rerun the query, select “Refresh All” from the middle of the Home ribbon. (If the icon
reads “Refresh,” click the down arrow for other options and select “Refresh All.”) You will see
the dialogue box for entering a parameter value again. Now, enter “Belina” to see her orders.
5. Close the query, saving it as CustOrderParamQry.
6. Parameters are not limited to finding exact matches. Any type of comparison that can be
done in a query criterion can use parameters. Before Access does the comparison, it gets values
for all the parameters in it and replaces the parameter indicators with those values. Entering a
parameter value “Adam” is the same as entering “Adam” in the query design grid. Re-create
ExpensiveProdQry from Chapter 10. As the query’s only criterion, however, do not use the
earlier “>1.6” Instead, enter “>[Products over what amount?]” (Without the quotation marks).
Usage Hint: Comparison operators include, besides “>” for “greater than,” these symbols:
= greater than or equal to
not equal to
= equal to (assumed if no comparison sign is entered)
IN followed by a list of text strings separated by commas, will match anything in the list
BETWEEN . . . AND . . . does what you would expect. This, by the way, is an example of where
you might want to use two parameters in the same criterion—for example, a starting date and
an ending date. There are more criteria, but these are enough for now. You know enough to
understand and use what you find in a Web will search for more in this area.
7. Run this query twice: first with a cutoff of $1.50, then with a cutoff of $5.
Usage Hint: If you use a dollar sign when you enter a currency amount in a parameter entry
box, Access is smart enough to ignore it.
8. Close the query, saving it as ExpensiveProdParamQry. Queries can calculate a column based
on other data in the same row. This can be data you choose not to show, as long as it comes
from a table in the query. Let us create a query to help our staff price out orders for more than
one of an item. (In a real store this would be done electronically, but the query is still a good
example.) Open a new query in Design view, select “ProdTbl” as its data source, and enter
“ProdName” and “ProdPrice” into the design grid.
9. In the top row of the first empty column, enter “2*ProdPrice” (without quotation marks),
make sure that column’s Show box is checked, and run the query. You will see something like
this:
The calculated values are in the column where you entered the expression.
10. Return to Design view, and look at the expression again. Access changed it in two ways:
(a) Access created the name we saw as its column name, Expr1. It precedes the expression,
separated by a colon. If we do not like this name, we can change it. If we know what we want
ahead of time, we can enter it when we define the calculated field.
(b) Access put square brackets around the field name. These are only required when there are
spaces in the field name, but Access plays it safe and puts them in every time.
11. Change the column name “Expr1” to read “2x.”
12. Enter expressions for 3x, 4x, and 5x. (It may be easier to copy and paste the 2x column, then
edit its column name and the multiplier in its calculation, than to key in each one from scratch.
It is definitely less error prone.)
13. Run the query to confirm that it works. Then close it, saving it as “ProdPriceQry.”This query
did not use selection criteria and did not combine tables, but it is still useful. We would not
want to store this information in the Product table because it can lead to data errors: When a
price changes, someone might forget to update the other columns or might make an error in
one of them. (A specialist would say such a table violates normalization rules by having columns
whose value depends only on other columns of the same table.)
14. It is easier to build complicated expressions with the Expression Builder. Create a new query,
using the same two fields from ProdTbl as above. In Design view, click the top row of the first
empty column and then select “Builder” in the Query Setup section of the Design ribbon. The
Expression Builder will open. It has two main sections as shown here:
• A window in which to build an expression.
• A series of three areas to select expression elements without keying them in. (That is always
an option, though.) If you click “>” so you can display it again.
15. We will create an expression to round up each product price to the next whole dollar.
Expand
Functions in the left pane of the bottom area, and select “Built-In Functions.”
16. In the middle pane, double-click “Math” as the type of function.
17. In the right pane, double-click “Int.” selecting the number placholder. The Int function takes
the integer part of a number: given 1.79, it will return 1. It appears in the top pane, with a
placeholder for the number.
18. Click the placeholder “number” to select it. Then expand the database name in region B,
expand Tables, select “ProdTbl” in the list of tables that drops down, and double-click
“ProdPrice” in the list of fields for that table in the middle pane. It will replace «Number» in the
expression.
19. Since Int gives the integer below the price, and we want the next whole dollar above it, we
must add 99¢ to the value it takes the Int of. (If we added $1, a price in whole dollars such as
$2.00 would become the next higher dollar amount, in this case $3.00. We do not want that to
happen.) With the insertion point between ProdPrice and the right parenthesis of Int, select
“Operators” in Region B.
In the middle pane, select either “” or “Arithmetic.” In the right pane, double-click the plus
sign. It will show up in your expression where the insertion point was. «Expr» shows where it
needs something to add. Click “«Expr»” to select it, and type “0.99” (without the quotation
marks). Then click “OK.”
Usage Hint:
going to the list of operators and then replacing “Expr.” However, it is important to know where
to find the full process and how to go through it, because it is often the best way to get
something done.
20. Back in the design grid, replace the name of this column, which Access has set to its default
of Expr1, to “Rounded Up.” (It may be easier to do this if you widen the column.)
21. Run the query. It shows 2 for every item price from $1.01 to $2.00, and so on.
22. The new column would look better formatted as currency. Return to Design view, select the
expression, and open the query’s Property Sheet (at the right end of the ribbon, in the
Show/Hide section, under the Design tab). Under the General tab of the property sheet, click in
the “Format” row (the second one down). Select “Currency” formatting from the list.
Usage Hint: You may have read in earlier activities that the Property Sheet is the “go-to place”
for most things that do not have ribbon icons. This is an example. We could have used
Expression Builder’s formatting functions instead, but they are more work.
23. Run the query again to confirm that the formatting is correct, close, and save it. Name it
“ProdPriceRoundUpQry” if you did not do so previously.
Deliverables:
1. Access database file with the 4 queries:
a. CustOrderParamQry
b. ExpensiveProdParamQry
c. ProdPriceQry
d. ProdPriceRoundUpQry
Be sure to use the correct naming convention when submitting any and all assignments, as
given below. The name for this assignment is: ACS12.
___
E.g.: 003_Newell_Jaime_ ACS12.accdb
Due: Prior to the next lab meeting
Submission direction: When you are ready to submit, login to Blackboard and navigate to the
“Assignments” button via the individual section link (NOT Meta. section link). Click on the link
that corresponds to this assignment. Browse to find your assignment file. If you are submitting
more than one file, click on “Browse my Computer” again to upload a second or third file. Once
all files are shown, click on SUBMIT (not "save as draft").