Open the Northwind database. Now let’s assume you’ve made various changes to your billing system and need to contact suppliers that will be affected by the modifications. Thus, you need to contact suppliers that:

a) provide beverages or condiments;
b) reside in Germany, Italy, or France (excluding those in Paris);
c) have an accounting manager listed as the main contact, but only if the firm is located in the United States, Canada, or Germany;
d) and have a Web site.
e) Additionally, you want to include Carlos Diaz, marketing manager for a supplier in Brazil, who helped you design the changes.

(NOTE: We’ll cover each of these conditions later in the article using the same labeling system of a, b, c, d, and e.)

In the Database window, select Queries and double-click Create Query In Design View. In the Show Table dialog box, double-click Suppliers to add it to the query. You also will need data from the Categories table to satisfy condition (a), so double-click this table, as well. Note that there is no relationship between the Suppliers and Categories tables, so to create the query, you’ll need to add a table that has relationships with both of these aforementioned tables. Double-click the Products table and then close the Show Table dialog box. If you wish, you can rearrange the three field lists for a better view (see the top portion of Figure 1). Close the query.

Let’s add contact data to the query. In the Suppliers field list, click CompanyName and, while pressing the SHIFT key on your keyboard, click Country to highlight a total of eight fields. Drag and drop the eight selected fields onto the design grid, and they’ll all automatically fall into the proper positions.

Add Criteria To The Grid

Now let’s set the criteria you need to meet the conditions we mentioned earlier (again, using the same labeling system of a, b, c, d, and e).

a) First, you need to contact any supplier who provides beverages or condiments. So, in the Categories field list, double-click CategoryName to add it to the grid; you may need to use the horizontal scrollbar to view the column on the grid’s far right side. On the Criteria row for the CategoryName column, type beverages OR condiments (capitalization is irrelevant, though) and press ENTER.

b) Second, you want to include any supplier who resides in Germany or in Italy, as well as those in France that aren’t located in Paris. From the Suppliers list, double-click Country and City. (Use the horizontal scrollbar to view the far right side of the graph.) Because their data is already included in the query, deselect their Show checkboxes. (The reason you’re adding them as additional columns is for the sake of setting up your criteria.) On the Or row of the Country column you just added, type Germany OR Italy. In the row below that same Country column, type France. In the City column you just added, on the same row as France, type NOT Paris. (You need to put these two conditions on the same row because both must be true at the same time to find a match.)

c) Third, you must contact any supplier located in Canada, the USA, or Germany whose contact has the title of “accounting manager.” Note that in condition b, we’ve already included all the suppliers in Germany, so we can ignore the Germany requirement here. Double-click ContactTitle from the Suppliers field list to add it to the grid, and deselect its Show checkbox. On the next empty row, type Canada OR USA in the Country column (the one you recently added). In the ContactTitle column (the one you just added) on the same row, type Accounting Manager.

d) Fourth, you need to contact any supplier who has a Web site. In other words, any record that has a value in the field used to enter Web sites should be included. You can find these records using the operator Is Not Null (records that don’t contain a null value) in the field. Double-click HomePage in the Suppliers field list to add it to the grid. In the next empty row, type is not null in the HomePage column.

e) Finally, as we mentioned earlier, we want to include the record of Carlos Diaz. Double-click the ContactName field in the Suppliers field list, and deselect its Show checkbox. On the next empty row, type Carlos Diaz. Open the File menu, click Save, type SC Suppliers in the field, and click OK. See Figure 1 to view how the design grid will look once you enter the criteria we suggested for this example.

Review Your Records

Click the View button on the far left side of the toolbar to see your records. Select the Company Name column and choose an Ascending sort. In Figure 2, you’ll see we’ve hidden some of the columns for a better view and added a Reason For Inclusion column to indicate why Access retrieved each record it includes in the collection.

Click View to return to Design view. Deselect the Show checkbox below the CategoryName and HomePage fields. Click View again, select the Company Name column, and choose an Ascending sort. You now have a list of names and addresses for the suppliers that meet your criteria.