We will now create two queries in the WorldCraft database to extract specific data.
First, you view the records that relate to each of the four suppliers, and then you use the two tables that you‘ve created to ask two questions, called queries. You create a query to find out how many products you have from Asia in the Art category, then you create a query to determine the number of products with fewer than 10 items in the inventory.
1. Open the WorldCraft Inventory database if it is not open already. If you see a ―Security Warning‖ when opening, click the options… button then select Enable this Content, click OK.
You may not see this Warning in the Delta computer labs.
2. Double click the WorldCraft Suppliers to open the form, then click the lower Next record button several times to view all the records associated with each of the four suppliers. See figure.
As a result of the one-to-many relationship you created between the Suppliers table and the Products table, you can create queries that list all the products purchased from a specific supplier.
3. After viewing all four suppliers and there associated products close the WorldCraft Suppliers form.
2
33 4. Now let‘s create our first query by completing the following steps:
a. Click the Create tab
b. Click the Query Wizard button in the Other group, then click OK to accept Simple Query Wizard.
c. Verify that Table:WorldCraft Products is selected in the Tables/Queries text box d. Click the Select All Fields button to select all the fields in the Products table e. Click Next
f. Click Next to accept a Detail query
g. Select the contents of the Title text box, press the delete key to delete the current text.
Type Asia Jewelry as the query name.
h. Click the Modify the query design option button, then click Finish. The query is now in Design view. See figure below.
i. Click the [Region] Criteria cell, type Asia as shown in figure above. Access automatically inserts quotations ―‖ around the criteria.
j. Click the [Category] Criteria cell, type Jewelry as shown in figure above.
k. Click the Run button in the Results group to display a datasheet showing the results of the query. See figure below.
Note: The results show all Jewelry products in Asia.
4i 4j
34 5. Print the query results by completing the following steps:
a. Click the Office button
b. Select Print then slide over to Print Preview
c. Click the next page button to see the next page. Note, in the current orientation it will not fit on one sheet.
d. Click the Landscape button in the Page layout group.
e. Click the Print button in the Print group. Click Ok . Place the printed results in your folder and label it DB-6 #1.
6. Close the query by clicking the close button (X) to the far right of the Asia Jewelry tab, then click Yes to save the query. (Note: the newly created Asia Jewelry query icon in the Navigation pane.)
7. Let‘s create another query by completing the following steps:
a. Click the Create tab
b. Click the Query Wizard button in the Other group c. Click OK to select the ―Simple Query Wizard‖
d. Select the Table: WorldCraft Products within the Tables/Queries pull-down menu.
e. Add all the fields by clicking the add all fields button , click Next.
f. Click Next
g. Change the title of the query to Items to Order, and click the Modify the query design option button.
h. Click Finish. You should now see the query in Design view.
i. Click [Units In Stock] Criteria cell, type <10. This is similar to steps 4i and 4j above.
j. Click the Run button in the Results group to show the results of the query.
k. A datasheet listing all the products with fewer than 10 units in stock appears. These are the items that you need to order.
8. Print the query results by completing the following steps:
a. Click the Office button
b. Select Print then slide over to Print Preview
c. Click the next page button to see the next page. Note, in the current orientation it will not fit on one sheet.
d. Click the Landscape button in the Page layout group.
e. Click the Print button in the Print group. Click Ok . Place the printed results in your folder and label it DB-6 #2.
f. Click the Close Print Preview button in the Close Preview group. Do not close the query.
35 g. Click the save button in the quick access toolbar to save the query.
9. Let‘s add a calculated field to the query by completing the following steps: (calculated fields are never stored in tables. They are always calculated when needed within queries.)
a. Click the View button to change back to Design view.
b. Click in the blank cell to the right of [Unit Price]
c. Click Builder button in the Query Setup group.
d. Type the formula: Units to Order: (10-[Units in Stock]) in the Expression builder towards the top of the window. Double check that all characters are typed correctly. If one character is miss-typed the calculations will be incorrect.
e. Click OK to except the typed expression. The expression is now entered in the column.
f. View the results by clicking the Run button. Review the new calculated field.
g. Change back to Design view by clicking the View button in the Views group.
10. Let‘s add another calculated field in the query by completing the following steps:
a. Click in the blank cell to the right of Units to Order. (See figure below).
b. This time we will not use the Expression builder to enter the formula, instead we will type in the formula directly into the cell. The expression builder is optional but can minimize on typing errors by creating formulas by clicking on objects.
9b
Formula: Units to Order:(10-[Units in Stock])
The first part ―Units to Order:‖ is the label portion of the formula. It shows up as the field heading when the query is run.
This formula calculates how many units you need to order if fewer than 10 items are in stock. For example, if 8 units are in stock, you need to order 2 units so that you always have at least 10 units.
9d
36 c. Type: Total:[Units to Order]*[Unit Price]
d. Because of the small text area, as you type the text will scroll off the screen. Be sure to type it correctly.
e. View the results by clicking the Run button. Review the second calculated field.
f. Observe the Total field, check to see if the calculations are correct. Note the dollar unit sign ($). We will need to change this to euros (€).
g. Change back to Design view by clicking the view button.
h. Click anywhere in the Total column, then right-click, select Properties…
i. In the property sheet, select the Format pull-down then select Euro. Close the Property Sheet by clicking the close box (X).
j. View the query results by clicking the Run button. Confirm the Euro sign replaced the dollar sign.
11. Print the query results by completing the following steps:
a. Click the Office button
b. Select Print then slide over to Print Preview
c. Click the next page button to see the next page. Note that even in Landscape orientation it will not fit on one page.
d. Click the Print button in the Print group. Click Ok . Place the printed results in your folder and label them DB-6 #3.
e. Click the Close Print Preview button in the Close Preview group.
f. Close the query results datasheet.
g. Be sure to save the query if asked.
10c.
Formula: Total: [Units to Order]*[Unit Price]
―Total‖ will be the field heading.
This Formula multiples ―Units to Order‖ by the ―Unit Price‖ calculating the amount needed for each item to be ordered.
10a
37