- By Andrew Couch
- Selecting all columns from a table
- Selecting individual columns from one or more tables
- Joining tables to see unmatched or missing records
- Filtering by single and multiple combinations of choices
- Adding calculations with the expression builder
- Returning the top matched records
- Eliminating duplicate values
- Creating a summary calculation
- Prompting to filter data with parameters
- Creating a crosstab query with the Query Wizard
- Simplifying a problem with a query by using other queries
- Adding two sets of query results together
- Resolving ambiguous outer joins
- Creating an additional query to resolve a problem with mixed joins
Eliminating duplicate values
Each row in a database table should contain unique and non-duplicated data. However, because using a query enables you to select only some of the fields, the values in those selected fields can be duplicated. This can be very useful when you are given a table of data from another source outside of Access and you want to identify a unique list of values in a particular field.
For our example, we have a list of Customer records where we have more than one customer in each city and we want to display a list of unique city names.
Eliminate duplicate rows
Select and optionally sort the field.
Eliminate any NULL values by typing Is Not Null in the criteria.
Click Property Sheet to display the Property Sheet, if it is not already shown. If your Property Sheet does not show the properties we have displayed, click anywhere on the gray background to see the query properties; the property sheet can display either query or field properties depending on where you last clicked in the query design tool.
Change the query property Unique Values to Yes.