669086.inddWe are pleased to announce that Microsoft Access 2013 Step by Step (ISBN 9780735669086) by Joyce Cox and Joan Lambert is now available for purchase!

Purchase

From Amazon click here
From O’Reilly Media, official distributor click here
From Ada’s Technical Books, an independent bookseller click here

Download free sample chapters

From the Microsoft.com download center click here

 

 

 

Excerpt

Below is an excerpt from Chapter 7: Create queries.

Creating queries manually


The query wizards guide you through the creation of common queries, but you create less common queries manually in the Query Designer.

image

This query was created manually in the Query Designer.

Let’s review the features of the Query Designer:

▪ In the top pane are field lists for the tables that can be included in the query.
▪ Lines connecting the field lists indicate that the tables are related by common fields.
▪ A table’s primary key field is indicated in its field list by a key icon.
▪ The Field row of the design grid contains the names of the fields actually included in the query.
▪ The Table row shows which table each field belongs to.
▪ The Sort row indicates which field(s) the query results will be sorted on, if any.
▪ A selected check box in the Show row means that the field will be displayed in the results datasheet. (If the check box isn’t selected, the field can be used in determining the query results, but it won’t be displayed.)
▪ The Criteria row can contain criteria that determine which records will be displayed.
▪ The Or row sets up alternate criteria.

When you create a query manually, you add field lists for the tables you want to use to the top pane of the Query Designer and either double-click or drag fields from the lists to consecutive columns of the design grid. You then indicate which field to sort the matched records on and which field values to show in the results datasheet. As with filters, the power of queries lies in the criteria you set up in the Criteria and Or rows. This is where you specify precisely which information you want to extract.


SEE ALSO For information about filters, see Chapter 4, “Display data.”

If you want to run an existing query with a variation of the same basic criteria, you can display the existing query in Design view, modify the criteria, and then rerun the query. However, it would be tedious to do this more than a couple of times. If you know you will often run variations of the same query, you can set it up as a parameter query. Parameter queries display a dialog box to prompt for the information to be used in the query. For example, suppose you know you are getting low on the stock of an item and you need to place an order for more of that product with the supplier. You might use a parameter query to request the name of a supplier so that you can identify other products you purchase from that supplier before placing the order. This type of query is particularly useful when used as the basis for a report that you run periodically.


In this exercise, you’ll create a query by manually setting it up in the Query Designer. You’ll add criteria to extract the records for specific dates, and then convert the query into a parameter query that requests the dates to extract at run time.

image

1 On the Create tab, in the Queries group, click the Query Design button to display a blank Query Designer and open the Show Table dialog box.

image

You select the tables you want to work with on the Tables page. You can also use existing
queries or a combination of tables and queries as the basis for a new query.

Let’s manually recreate the Customer Orders query from the previous exercise.

2 In the dialog box, double-click Customers, double-click Orders, and then click Close.

TIP To add the field list for another table to an existing query, display the Show Table dialog box at any time by clicking the Show Table button in the Query Setup group on the Design tool tab. You can also drag the table from the Navigation pane to the top pane of the Query Designer. To delete a table from a query, right-click the table’s field list, and then click Remove Table.

3
Double-click the title of the Customers field list to select all the fields in the list. Then point to the selection, and drag down to the Field row of the first column in the design grid. Notice that the fields occupy consecutive columns and that the Table row of each column designates the Customers table as the source of the field.

TIP The asterisk at the top of each field list represents all the fields in the table. Dragging the asterisk to a column in the Field row inserts a single field that represents all the fields, meaning that you cannot then manipulate the fields individually.

4 Scroll the grid to the right, and click in the Field row of the next blank column. Then in the Orders field list, in turn double-click the OrderID, OrderDate, ShippedDate, and RequiredDate fields to add those fields to the next four columns.

image

You have added fields from two tables to the design grid.

5 Point to the gray field selector above the PhoneNumber field, and when the pointer changes to a black down arrow, click to select the column. Then in the Query Setup group, click the Delete Columns button.

KEYBOARD SHORTCUT Press Delete to delete the selected column. For a list of keyboard shortcuts, see “Keyboard shortcuts” at the end of this book.

6
In the Show row, clear the check boxes of the OrderID, Country, Address, and CustomerID fields.

7 In the Sort row, set the PostalCode field to Ascending.

8 In the Results group, click the Run button. Check the datasheet to ensure that the query produces the correct results.

Let’s make this query return the records for a specific range of dates.

9 Switch to Design view. In the Criteria row of the OrderDate field, enter the following, and then press the Enter key:
Between 1/1/2012 And 1/8/2012

TIP When you enter the A of And, Access displays a list of operators you might be intending to use. You can click an option in the list to save yourself a few keystrokes. In this case, ignore the list, and it will disappear.

10 Widen the OrderDate field to fit its contents, so that the entire criterion is visible in this field.

image

Access has added # signs to designate a date format.

11 Run the query to extract the matching records.

image

Only five orders were placed in the requested period.

Now let’s have the query request the range of dates each time you run it.

12 Switch to Design view. In the Criteria row of the OrderDate field, replace the existing criterion with the following, entering this criterion exactly as shown and then pressing the Enter key:
Between [Enter the beginning date:] And [Enter the ending date

13 Run the query, which opens the Enter Parameter Value dialog box.

image
The first Enter Parameter Value dialog box requests the beginning date in the range.

14 In the text box, enter 1/8/12, and click OK.

15 In the second Enter Parameter Value dialog box, enter 1/15/12, and click OK. Notice that the results datasheet lists only the nine orders placed between the specified dates.

16 Save the query as Orders By Date.

image