Query for when an appeal was last used

Sherill Charlot

I am trying to make a query on when an appeal was last used, so that I can clean up my database.  Right now I have an appeal query type in the dymanic format.  In the criteria I used the "one of" operator, so that I could select all of my appeals.  In output I added the Appeal ID, Appeal Description, and Gift Date.  This sort of works except it duplicates the appeals.  I need help figuring out the best way to make it so I can see the most recent date the appeal was used. 

Ryan Hyde

I'm not sure about the appeal query approach. What I'm going to suggest is using a gift query and connecting that to a pivot report.


1.) Create a gift query where the criteria says "Appeal ID <one of> [appeal1, appeal2, etc.]"

2.) In the output of that query, grab Appeal ID and Gift Date (note: it is critical that you include this in the output, otherwise the pivot report will have nothing to look at)

3.) Save this query

4.) Go to Reports > Pivot Reports > New

5.) On the Genreal tab, attach your query

6.) Click Generate

7.) Drag Appeal ID into the lower left hand box ("Row Labels") on the right side of the screen

8.) Drag Gift Date into the lower right hand box ("Values")

9.) There's a little black arrow to the right of the "Count of Gift Date" thing you just dragged into the Values box. Click that and then click on "Value Field Settings"

10.) In Value Field Settings, select "Max" from the list of options and hit OK

11.) Highlight column B and then format the contents as date (it defaults to a numeric value instead, which is nonsense)


Presto, you have the maximum gift date related to each appeal. Do with that what you will.

Sherill Charlot

This is so awesome and works for what I am trying to do! Thanks so much, I haven't used pivot reports yet, so I wasn't aware of what they can do.  Thanks you again for your help with this question Ryan!!

Ryan Hyde

I'm glad I could help


And when you're done with this, play around with pivot reports some more. They're a very useful tool for lots of different kinds of things.