Query

JoAnn Strommen:

Linda,

So in suggestions do you get what they mean by one-to-many fields and why you're gettting duplicates?  Basically any field that a record can have more than one of can cause duplication in query if it's an output field.  Like constituent code, phone, relationships, attributes.  Sometimes you can avoid by being specific in output like just selecting a specific attribute or relation type.

Query is a grouping tool, Export is a reporting tool.  If you take your query to Export function, export pulls each record only once.  And once you  start using and saving your exports you can pull any query in to export say mailing label fields.  You don't have to make them all part of your query.  Just group the records you need in the query and then go to Export. 

(You can remove records from a Query List but if you have a lot of duplicates I find Export a lot easier.)

Below is solution from knowledgebase that explains it more thoroughly.  I know it can be irritating at first but once you get the hang of it, it actually make sense why you get the duplicates.  Sometimes checking the 'suppress duplicates' box works - see below.

https://www.blackbaud.com/kb/index?page=content&id=BB61118&actp=search&viewlocale=en_US&searchid=1393716000058

Why are there duplicates in the query results and how to remove them? (includes demo)

Back to Search ResultsEmail PagePrinter FriendlySubscribe

 

Solution ID:BB61118Product:Accounts Receivable 7, The Raiser's Edge 7, Accounts Payable 7, Blackbaud CRM, Admissions Office 7 (EE), General Ledger 7, Registrar's Office 7 (EE), Registrar's Office 7 (BBSIS), Admissions Office 7 (BBSIS), Cash Receipts 7, Payroll 7, Fixed Assets 7, Student Billing 7Published:07/25/2002Updated:03/14/2012

Description

Query is a way to group records; it is not a reporting tool. The query output shows you whether the record belongs in the query. If a field with a one-to-many relationship is in the filters, output, or sort, the record appears 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.

This knowledgebase solution contains a demo.

Environment

Answer

Before performing the following steps, make sure you are using the correct query type. For example, you 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.

Watch the Duplicates in Query demo (4 minutes).

To eliminate the duplicate records:

Use the query in another module such as Export, Reports, or Mail

Or

Export the query to Excel and hide or delete the duplicates

JoAnn:  Thank you very much -- I appreciate it and think it finally make sense.