- Make sure you are using the correct query type or source view. Choosing the right source view when designing your query is very important. This decides what type of records you are asking to see in your results. So ask yourself, do you want to see constituent records? Membership records? Sales orders? etc.
- Including one-to-many fields in your output could be a reason why a record appears multiple times; A one-to-many field or relationship represents a situation in which one data value can have any number of associated elements.Click here to learn how to determine if a field is one-to-many.
- Example:There is a Revenue Query where the criteria is set for the results to show all constituents who have a Total Giving greater than $500. If you output only the Constituent Name field in Results Fields to Display you will receive 30 results. If one then outputs revenue\date they then receive 150 results. This is because although the constituent's total giving is greater than $500, the query will show you a line in the results for each date that particular constituent contributed to the total of $500. Revenue/Date is a one-to-many field in this instance.
- The most popular example of one-to-many fields would be to output a constituent's contact information. Outputting contact information such as Address, Phone number or E-mail address can cause multiple records to appear. Constituents can have multiple addresses, phone numbers, or e-mail addresses on their records. If you do not specify you want to see their primary contact information in the query you will see all the contact information. Click here to learn how to include one email address or one phone number in a query output.
- Another reason for multiple records could be that both members of the household meet the criteria so therefore you get a line for each individual. Most organizations prefer both members of the household appear on one line without excluding individuals who are not a part of a household. Click here to learn on how to report on individual and household cumulative giving without producing duplicates for multiple members of a household.
- If you are using a revenue query and the constituent name, amount, and date are all the same, the duplicates may represent the order and the payment. In the first tab of query, click on Revenue at the top left, then Transaction Type in the middle, then move it to Results Fields to Display. Preview your results and if one line says order and the other similar line says payment, this means that one line is for the sales order record and the other one is for the payment record. Go back to the first tab of query, click on Revenue at the top left, then Transaction Type in the middle, and move to Include Records Where. Set the criteria to equal to Payment.
You may need to output results to a CSV or Excel file with one-to-many values in multiple columns for the same record rather than as duplicate rows. You can see how Export Definitions and Query Results are different, learn how to create an export definition, and how to create an export process that uses the export definition and query that you created.