Properties are characteristics that define the various elements in your database. You’re probably most familiar with field properties, such as Field Size, Format, and Validation Rule, which are set in the lower pane in Table Design view. But controls, objects, and the database have their own property sheets that determine key traits.

Queries are no exception. Let’s take a look at three query properties that can help you tailor results to your information needs.

 Unique Values Property

Suppose you’re the purchasing manager of a widget company that’s reviewing its security risks. The chief operations officer asks you for a list of countries where the firm has suppliers.

This doesn’t seem like a difficult task. You know you have a table that has contact information for suppliers; it includes a field that identifies the supplier’s country. But there is a catch: because you often have many suppliers in the same country, several country names are repeated over and over. How can you generate a list that will show each country name just once?

One solution is to create a query and use the Unique Values property to retrieve only unique records. Let’s see how it works and how you can use it to get that list of country names.

 Create A Query

Open the Northwind.mdb database in the Samples subfolder of your Microsoft Office folder. Assuming you have made a copy (or have a backup) of the Northwind database, open the file. In the Database window, click the Queries button. Click New, choose Design View, and click OK.

In the Show Table window, choose Suppliers. Click Add and then click Close. Click the Maximize button in the upper-right corner of the window for a better view.

In the Suppliers field list, double-click CompanyName; scroll down the field list and double-click Country. To alphabetize the country data, click in the Sort row of the Country column, click the down-arrow button, and choose Ascending. Click the View button to see the various companies and countries. Notice that several country names display more than once. There are 29 records in the list.

Click View to return to Design view. Click the View menu and then Properties. Click in any open space in the top pane of the Query Design window. The title bar of the property sheet should read Query Properties.

Click in the Unique Values property. The default setting is No. Select the text and type y to change the option to Yes (or just double-click No). Click View again. Nothing has changed: You get the same 29 records you did before. Click View, click in any open area in the upper pane, and change the setting for the Unique Values property back to No.

 Modify The Query

Why did changing the Unique Values property to Yes have no effect on the records retrieved? Because each record comprises two fields, CompanyName and Country, and each of those combinations is unique. Repeating values in one field does not make any of those combinations by themselves less unique.

Let’s continue to explore how this property works. Move your mouse pointer to the Column Sector for the CompanyName column. When the pointer changes to a down arrow, click to select the column. Press DELETE to delete the column. Click View. With the Unique Values property set to No, you’ll see several country names repeated.

Click View and change the Unique Values property to Yes. (Refer to the instructions above if you need some help.) Click View. Because the query contains only one field, setting the Unique Values property to Yes eliminates the duplicate records. In other words, each country displays only once. Click View to return to Design View. On the property sheet, change Unique Values property back to No.

 Output All Fields

Suppose you’d like to get a quick look at all the data in all the fields in the field list. The Output All Fields property lets you do this easily. On the property sheet, change the Output All Fields setting by clicking the down-arrow button and choosing Yes. Click View.

All the records from all the fields now display. Note that the Country field displays as the first column, but you won’t see it repeated in the datasheet. Click View and click the Show box in the Country column to deselect it. Click View again. The Country data now displays after the Postal Code field because it’s in the underlying table. Click View.

 The Description Property

When you create a query, you may sometimes wonder, "Where can I remind myself and tell others why I created it?” The Description property can help you out.

First, let’s save the Save. Type Smart Computing Testand click OK. On the property sheet, click in the Description property. Access may have supplied a description for you; but you can delete it and insert your own. Press F2 and type Learn about query values.

Press CTRL-S to save the query and then close it. In the Database Window, choose View and Details. Scan down the list of queries and note the description you added for Smart Computing Test.

If you wish to delete the query you created, right-click it, click Delete, and click Yes. To return to your original Database Window display, choose View-List or click the List button.