Extend Microsoft Access Applications to the Cloud: Creating Data Sources by Using Queries
- 2/9/2015
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.
TIP
Although you cannot create a “make table” query or “append” query in a web app, you can create an empty table and then use SSMS to execute an “append” query to populate the data.
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.
){kind=link}
FIGURE 6-27 Specifying the Unique Values setting for a query.
TIP
When you specify unique values in the Azure SQL Database, the SELECT DISTINCT syntax will be added to the query. An equivalent technique to using the unique values property is to use a Totals query and the Group By operation.
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.
){kind=link}
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).
TIP
This method, although it makes queries very quick to construct and avoids the need to create a new table, does not allow you to enter a new value for City, which is not already in the list of values. In this case, you need an alternative view, such as a datasheet view, to enter a new value.