Excelsior!

A Raiser's Edge query is a great tool for comparing specific values.  For instance, if you want to find every record that has two $50 gifts, you can do so by creating a constituent query with the criteria Summary Information, Total Number of Gifts equals two, filtering on Gift Amount equals $50.

But what happens if you want to find records that have two gifts with the same amount, regardless of which amount you’re using?

If you have a great deal of patience and nothing else of any consequence to achieve in the next several months, you could set about using endless OR operators, or merging queries to find these records, but I wouldn’t recommend it.

There’s also the higher math approach, which is to use RODBA to write a SQL query, but if you’re a Plain Jane end user like I am, that isn’t an option either.

There is a third way to find these records, to compare one field against another without the tedium of entering every permutation into a query or using a more advanced tool like RODBA.

Sometimes you just have to be smarter than the software.

I have used Knowledgebase solution BB16868 many times as a skeleton key to get the information I need.  The solution walks you through the process of finding constituent/spouse pairs that have the same (or different) last names and popping the records into a query for you.

The process involves query and export, which are familiar to most, as well as the great boogeyman of The Raiser’s Edge – Import.  Don’t let the involvement of Import discourage you from trying this solution – there is only one field to import and you don’t even change anything on the records.

Depending on what fields you need to compare, you can either create a query of your records or export every record in your database.  When you select your fields to export, you will only need to include three – the record’s Import ID, and the two fields you wish to compare.  The KB solution compares constituent and spouse last names, but you could compare two different phone numbers on a constituent, or even Gift Amount and Soft Credit Amount on a gift.

Extensive knowledge of Excel is not required to use this solution. Assuming that you have a header row, and that the Import ID is in Column A, the solution you would enter in Column D would look like this:

=IF(B2=C2,"Yes","No")

Once you have entered this in Cell D2, copy it all the way down the rest of the column.  For every record where the values in columns B and C match, you will have a “Yes” in Column D. Contra wise, if the records don’t match, the formula will return a “No” value.

Once Column D has been populated, your goal will be to winnow down the spreadsheet until only the records you want remain.  Your first step will be to sort the spreadsheet by Column D, so that you have all the “Yes” records in one group, and all the “No” records in another.

Using the example from the KB solution, if you want all the constituents whose last names do not match their spouses’, you would delete all the rows containing the “Yes” records.  When only the “No” records are left, delete columns B, C, and D – you won’t need them where this process is going.

When the Import ID column is the only one left standing, save the spreadsheet as a .csv file and go to Admin, Import in The Raiser’s Edge.  Create an Import and select the .csv file you’ve edited and saved as the file to Import.

At this point, the process pretty much comes down to marking two options. The first of these is on the General tab, where you will mark Update existing records.  Remember, since the Import ID is the only field you are importing, you won’t actually be making any changes to the record – you’ll just be tricking the software into thinking you are.

The other option to mark is on the Summary tab – Create an output query of records updated.  When you click Update Now, you will be prompted to save that Query – once the Import is completed, you will have the query of the records you need.

This process does take you a few paces off the beaten path, but it’s a handy way to put together a query when a more straightforward approach isn’t an option.