Unique values in queries
When you create a query, one row will be displayed for each underlying record, regardless of which fields you choose and whether the output shows several records with the same value. The Unique values property enables you to eliminate any duplicate values and makes the output of each record unique across the combination of selected fields. These queries are useful when summarizing data, making lists of unique choices, and when re-organizing data to identify new lists of unique combinations with which to construct new tables. These queries are read only.
As an example of this, Figure 6-27 shows a query to select the City field from the Customers table and display a list of unique values when clicking on the Unique Values icon on the design ribbon. We also add a criteria to exclude any null values and sort the results.
FIGURE 6-27 Specifying the Unique Values setting for a query.
You can then edit your customers view and replace the text box for the City field with an Autocomplete control, which uses the query qryListOfCities as the row source for the control, as shown in Figure 6-28.
FIGURE 6-28 Using a Unique Values query in an autocomplete control.
In Figure 6-29, you can see the result of using the autocomplete control to display a list of values for the City selection based on the unique values used in the existing customer records.
FIGURE 6-29 Completed view with autocomplete control.
In Figure 6-29, the autocomplete control sorts the results based on matching characters. The word London is displayed before Berlin, because the letter l is first matched as the first letter in the word London. This means that the autocomplete control will override any sorting you specified earlier when constructing the underlying query qryListOfCountries (where you had an ascending sort on the City name).