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.

Before following these steps, make sure you are using the correct query type. For example, let's say you've create a payment query and a record has five payments that meet the criteria. All five payments display on the Results tab but they are not duplicates. Each payment is a separate record because a payment query looks at each payment. If you want to look at the entire record, run a Record query instead.

To minimize the appearance of duplicate rows in the query results:

  1. Limit the number of output fields, especially one-to-many fields, such as phone number.
  2. 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.
  3. Suppress duplicate rows:
    1. Select Tools, Query Options from the menu bar of the query
    2. 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.

To eliminate the duplicates:

Use the query in another module such as Export, Reports, or Mail; or export the query to Excel and hide or delete the duplicates.