Query question

Gloria Sulkowski · May 2015

When I run a query and I need the last gift amount and the last gift date, I always get all the gifts received instead of just the one I want. What am I doing wrong?

Any help would be greatly appreciated.

Thanks,

Gloria

Lauren Halterman · May 2015

Is this a constituent query? What other criteria are you using?

I tried it by running a constituent query of staff and selected gift date and gift amount for out put and I'm only getting one gift for each, so it works for me.

Jennifer McHale · May 2015

Gloria,

What type of query are you running? If it's a constituent query, you can select from under "last gift" in the criteria.

I believe there is something similar in the gift queries - I just don't use them as often to know if off the top of my head!

Gloria Sulkowski · May 2015

Lauren Halterman:

Is this a constituent query? What other criteria are you using?

I tried it by running a constituent query of staff and selected gift date and gift amount for out put and I'm only getting one gift for each, so it works for me.

Lauren,

I'm using a constituent query and Fund types (Scholarships). I selected last gift date and last gift amount and I get multiples.

I have tried doing a fund query and a gift query and I still get multiples. Any suggestions?

Thanks,

Gloria

Jody Johnson · May 2015

What is the output that you are requesting, i.e. name, address, etc? Also, what is your criteria?

John Heizer · May 2015

Here's the RE mantra: "Query is a grouping tool, not a reporting tool." You're getting the right people. Use that Query as the source for an Export and then you'll get only one record per Constituent.

Gloria Sulkowski · May 2015

Jody Johnson:

What is the output that you are requesting, i.e. name, address, etc? Also, what is your criteria?

Jody,

This is what I need to find out what the last gift amount was, last gift date, name of donor, fund description and fund ID. For my criteria I'm using Fund type (Annual Scholarship, Endowed Scholarships, etc.) How can something so simple be so complicated? :-)

Thanks,

Gloria

Gina Gerhard · May 2015

John - Thanks for running interference! When you hear output you shoudl think 'export' - not query, especially for gift data ....

John Heizer · May 2015

I don't know if this will apply to your particular scenario, but one "gotcha" to look out for is when using "Last Gift" as criteria in a Constituent Query. If you query on, for example, "Last Gift > Funds > Fund Description = Annual Fund FY14" it will only pull people who have not given ANYTHING since that gift to that fund. If they've since given to anything else they will not be returned in your results.

Our museum memberships are included in RE, but they're not "philanthropic" giving, so when I want to pull people whose last philanthropic gift was to Annual Fund 14 I have to take into account that they may well have a membership purchase that is now their "Last Gift" and they won't be pulled if I use the criteria shown above.

There's some good information on this here: http://bbmarketplace.force.com/bbknowledge/articles/Article/41525 and here: http://bbmarketplace.force.com/bbknowledge/articles/Article/52143

Jody Johnson · May 2015

Gloria,

If you are running the query with multiple funds, then the last gift a donor made to any or all the funds will be included. For example, if I gave to the annual scholarship and endowed scholarships both will show up. If that is what you want to see, then there will be duplicates. If you want to know what the last gift I gave was, then don't funds in the criteria, use gift amount greater than zero. It will then list only one gift for me. You can add the fund description and ID to the output tab and it won't affect the results.

Another reason that a person might be showing up multiple times is that you have split gifts. If you add fund split amount, it will show the gift properly split up, so if there were two funds, you will see them listed twice and the gift amount will be for the amount to each fund. If you just use the gift amount, you will only see the gift once.

If you email me your query control report, I can see exactly what you are doing and help you to further trouble shoot.

email: jody.johnson@cancer.ab.ca

Patti Hommes, bCRE-PRO · May 2015

Lots of really good answers and I agree with our colleagues that export is the way to go!

Ryan Hyde · May 2015

I'm in agreement with the "export" mantra. That said, there's a semi-hidden feature in Query that sometimes does the trick when you're trying to do a quick and dirty data pull.

In your query, look at the top menu bar and go to Tools > Query Options > Record Processing, and then check the box that says "suppress duplicate rows."

Depending on the reason for duplication (the issue can be either in the in put or the output portions of your query), this will eliminate the problem. It doesn't work in all scenarios though.

When in doubt, though, use an export.

Christine Cooke 2 · May 2015

You should be using a query to choose the population or group of donors you want to look at and then you should be using Export to look and just the last gift field. Or if you want the LG field and a gift history, you can define all of that in Export and it will not be so messy.

Query question

Comments

Categories