Help with query ... and export/output of said query

Sandra Thompson · April 2015

I created a constituent query Summary Information of gifts greater than or equal to 1,000, then gave a date range, gift type, excluded the campaign – fund– appeal. When I hit the results is says 120 people, which is correct. However, once I go into the Output tab (added name, gift date, fund description, and gift amount) it will give me over 3000+ from all different dates. It is like it ignores the critriea tab all together. I need to be able to isolate just the original 120 and get information for just that grouping to export into excel. Help please

Nicole S · April 2015

Check the KB -- Also, remember that query is for grouping and export is for exporting. If you run that query through export you should get the 120 results that you are looking for. Make sure you are using the right type of query too -- if you want a list of consituents use a constituent query/export, but if you want a list of gifts you need a gift query/export. Basically.

Why are there duplicates in the query results and how to remove them? 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 BB61118

Joshua Bekerman bCLO bCRE · April 2015

Use an Export instead, using the query for the records to include. You are getting more records in your results because it is displaying the constituent info for each of their gifts. The query criteria does not apply to the gift information on the output tab.

Josh

JoAnn Strommen · April 2015

Sandra,

This is a perfect example of how one-to-many fields add records to query and why query is a grouping tool, not an export tool. When you output a field like gift date/fund/gift amount it will pull every gift date/fund/gift amount. But it's not that hard to work with. Just take your query with results of 120, save it. Go to the Export function of RE. You'll want a constituent export, excel file. Use your query for the records to include and then in this function select the fields you want to output. It does work really well. You'll get one line per record with all the fields you choose to output.

Post again if you run into trouble.

JoAnn Strommen · April 2015

JoAnn Strommen:

Sandra,

This is a perfect example of how one-to-many fields add records to query and why query is a grouping tool, not an export tool. When you output a field like gift date/fund/gift amount it will pull every gift date/fund/gift amount. But it's not that hard to work with. Just take your query with results of 120, save it. Go to the Export function of RE. You'll want a constituent export, excel file. Use your query for the records to include and then in this function select the fields you want to output. It does work really well. You'll get one line per record with all the fields you choose to output.

Post again if you run into trouble.

Sandra, don't want to cause confusion down the road. You can use either query as both are pulling 120 constituents - one just shows multiple listings due to the output fields. Either query can be used in Export (this is the separate function,not just the icon on the query window) and will/should give you the 120 records.

For your export do you need to see each payment info? That will be a little more work to export each gift amount and gift date. You'll need to estimate the largest # of payments on a record and export the gift date/amount info that many times when it asks how many you want to output.

Sandra Thompson · April 2015

What I need to see is only people that gave cumulative gifts that amounted to 1,000.00 and over ….if you gave 100.00 every month that totaled out to be 1,200.00 then I need to see each date (each month you gave) and I need to see which funds you gave to as well. I am lost as to where to get a report/query that can give me just those specifics. Like you said it is giving me every gift from forever! Thanks for helping

JoAnn Strommen · April 2015

Sandra Thompson:

What I need to see is only people that gave cumulative gifts that amounted to 1,000.00 and over ….if you gave 100.00 every month that totaled out to be 1,200.00 then I need to see each date (each month you gave) and I need to see which funds you gave to as well. I am lost as to where to get a report/query that can give me just those specifics. Like you said it is giving me every gift from forever! Thanks for helping

So, take your query that grouped the 120 records that meet your criteria to the Export module. New Export > Constituent, format Excel. For Include - select your query. Then on output tab select any bio date that you want and then select Gifts > Gift Amount. On that tab then put 12 for # of gifts to export if that is the most any donor would have. Select date info, order gifts by date, filters, gift types etc. After you have all that criteria. Select Gifts > Gift Date also Gifts > Fund.

You don't need to output the summary info here as you want detailed gift info. The summary you used in query grouped the records you need.

One neat feature of Export is the preivew - right up below the word 'tools' there is an icon that you can click and it will show you the first 15 records you'll be exporting and all the fields you have selected.

Give it a try - you really can't mess anything up by trying.

Susan Connors · April 2015

Another way to do this in query - and not export, though that is definitely a good option - is to run your query with no output, then create a new query that points at that first query (Tools, Query Options, Record Processing, Select from query). Then you can pull summary information without duplicate rows. However, if you are pulling any individual gifts info, you will get all those extra rows. Just don't pull any one-to-many fields. This is a bit of a shortcut just to review your records, without having to look at an exported spreadsheet.

JoAnn Strommen · April 2015

Susan,

That's an interesting work around - haven't tried pulling using another query.

However, in Sandra's case I think it's just creating an extra step. The summary information was not giving her extra rows - don't know of a case where it would. Pulling the one-to-many fields as you said will cause the duplications and that was the issue to begin with as that's the info she needs.

Gina Gerhard · April 2015

And I'll just add here what I've heard from Blackbaud repeatedly over the years - do not EVER output gift data out of a query!

You definitely want to use Export to ensure that the gift data is correctly calculated and processed.

John Heizer · April 2015

You need a Constituent Query that determines WHO will be included and then a Gift Export (based on that Query) that will pull the Gifts you want for that set of Constituents.

Create a Constituent Query that uses Summary Information > Summary for Gift > Total Amount of Gifts > greater than or equal to $1,200 and then filter by the date range and any other relevant information. For the Ouput of the Query you only want to add the Constituent's Name, maybe Constituent ID. If you add any Gift fields here you'll end up seeing lots of duplicate rows that can be confusing. Save that Query.

Start a Gift Export and use your new Query as the source to "Include...". On the Outupt tab, note that in the right pane of the window when you click on the "New Gift Export" you have a "Criteria" button available at the bottom of the pane. Here is where you can filter and Export the specfic Gifts for each of those chose Constituents. This will Export one row for each gift that meets your criteria, repeating any Constituent inormation you might include on each of those rows.

JoAnn Strommen · April 2015

Sandra,

I hadn't thought about a gift Export as John suggests. That will give you each gift on a separate line with your constituent data. Might be easier to view that was for 120 records (probably not for 1200 records). The previous posts' directions will get you 120 lines with each person having their record and all their gifts listed out to the right. Basically horizonal data vs. Gift export which would be vertical. Both should pull data you need correctly.

Sandra Thompson · April 2015

John Heizer:

You need a Constituent Query that determines WHO will be included and then a Gift Export (based on that Query) that will pull the Gifts you want for that set of Constituents.

Create a Constituent Query that uses Summary Information > Summary for Gift > Total Amount of Gifts > greater than or equal to $1,200 and then filter by the date range and any other relevant information. For the Ouput of the Query you only want to add the Constituent's Name, maybe Constituent ID. If you add any Gift fields here you'll end up seeing lots of duplicate rows that can be confusing. Save that Query.

Start a Gift Export and use your new Query as the source to "Include...". On the Outupt tab, note that in the right pane of the window when you click on the "New Gift Export" you have a "Criteria" button available at the bottom of the pane. Here is where you can filter and Export the specfic Gifts for each of those chose Constituents. This will Export one row for each gift that meets your criteria, repeating any Constituent inormation you might include on each of those rows.

thanks guys!!! I ended up having to do exactly as you suggested John.

Help with query ... and export/output of said query

Comments

Categories