Duplicate Rows in Constituent Query

Question

Hello everyone, I am trying to run a basic constituent query but I keep getting duplicate rows. Here is the criteria:

and here is the output:

When I run the query, the results have multiple rows for each constituent, even though the information in them is the same (i.e. same name address, codes, gift date, addresses, etc.). How can I get this down to one row per constituent? Forgive me if this is basic stuff, I am new to RE.

Alex Wong · Answer

Query is powerful and can be used as exporting tool once you understant the underlying reasoning behind query, and if you are the database person for your org, you will benefit from getting better understand of Query and why duplicates exists. You can see this post where I gave a pretty long response around this topic.

I want to dispel a myth here, the more output field, the more liekly you will get dup. This is not true.

ONE-2-MANY relationship field in the Filter, Output, or Sort tab of Query will give you duplicates. Example:

Constituent Query:

* Filter* Preferred Country = United States

* Output* All bio: Constituent ID, Title, First Name, Last Name, Spouse First Name, Spouse Last Name, Gender, Martial Status
* All Preferred Address info: Preferred address lines, Preferred City, Preferred State, Preferred Zip, Preferred Country
* Summary Data: summary of gift with subfilter for giving in 2022, 2023, 2024, 2025, etc

* Sort* summary of gift with subfilter for lifetime giving

This would not result in any duplicate rows in the constituent query as all the field used in Filter, Output, and Sort tab are ONE-2-ONE with a constituent (a single constituent can only have ONE of such field)

The moment you add Constituent Code to filter, output, or sort tab, you will get duplicate, b/c a single constituent CAN have more than ONE constituent code.

So back to your original query's dup, like Tatyana said, Gift Constituency is ONE-2-MANY on a constituent (ONE constituent can have more than one gift.

Dariel's suggestion will work if your intent is more a gift query, howver, if you still want a constituent query, you can do this:

Constituent Query:

* Filter* Summary of Gift Amount (or # of gift works too) > 0* subfilter = gift constituency of Sunbeam - Sustainer

* OR Summary of Gift Amount > 0* subfilter = gift constituency of Sunbeam - Active

* Output* from the LEFT panel with all the field, first section = "Query" section, you can select the 2 summary of gift amount field you used in the Filter, and output that
* all the other output you have (do not output Gift Constituency)

Rachael Haffey · Answer

I will give that a try, thank you!

Dariel Dixon · Answer

As usual, @Tatyana Leifman is right on the money.  @Rachael Haffey, I think this would be better run as a gift query than a constituent query, as the fields you are looking for are primarily gift based.  You may have better luck there.  If you look at the fields you're pulling you should be able to determine which field is a one-to-many field for the query.