Query is a way to group records; it is not a reporting tool. The query output simply shows you whether the record belongs in the query. If a field with a one-to-many relationship is in your filters, output, or sort, the record will appear multiple times-- once for each time the record meets the criteria. This allows you to check your query to ensure you get the expected results.
For example, if the Phone Number field is selected in the output and a person's record has three phone numbers, the person appears three times in the results, once for each phone number.
Note: The record is pulled into the query only once. Based on your criteria, output, or sort fields, it may display multiple times. Because query is not a reporting tool, we recommend using the query to filter Export, Reports, Mail, etc.; each record will only display once when used as a filter.
To minimize the appearance of duplicate rows in the query results:
- Limit the number of output fields, especially one-to-many fields, such as phone number.
- Increase the number of filters. For example, when filtering on phone numbers, also filter on the phone type to limit it to one specific phone type.
- Suppress duplicate rows:
- Select Tools, Query Options from the menu bar of the query
- On the Record Processing tab, mark the Suppress Duplicate Rows checkbox.
Note: Marking this checkbox prevents rows from duplicating; however, if the query output includes fields with one-to-many relationships, such as phone number, the 'duplicates' may still appear. This is because the rows are not exactly the same and therefore not seen as duplicates. For example, one row contains the name and home phone number. The next row contains the name and cell phone number.