With Query, you can group records according to whatever criteria you want. Before you even begin to build your query, you need to think about what records you want to see. Form a statement, such as “I want to see the names of all constituents who are members and have given a gift this month, listed alphabetically by last name” or “I want to see all the revenue that came in last month, grouped by date.” Be as specific as possible. You can even write down your statement to help you when begin to build your query.
Note: To access the Query section, from Analysis, click Information library.
For this guided tour, let’s use “I want to see the names of all constituents who are members and have given a gift this month, listed alphabetically by last name.”
Now that we have our statement, let’s start with the basics of query.
Queries are all about fields. Remember when you entered a birth date on the Personal tab of a constituent record? That’s the Constituents: Birth date field in a query. There are definitely a LOT of fields available in Query. But Query really is just a list of all the fields on all the different types of records in the program. Ultimately, you just need to know where to look in the list. Noodles? Aisle 5 in the grocery store. Date of revenue for constituents? Constituents > Revenue: Date. We'll talk more about how to find this field in a bit and each part of the path to find it. For now, rest assured that you will be able to find the fields you need because we put together a cheat sheet with some of the most common fields used in queries. Take a look at Common Query Fields. Even better, we put together a cheat sheet of common query filters. Check out Common Query Filters.
Remember, Query is a tool to group records; it’s not a reporting tool. You should browse the results of your query to ensure you set up your query correctly!
You might even see (or think you see) duplicates in the query results. What if you wanted to see all the constituents who gave money last month? If I gave twice last month, I’d appear twice unless you summarized the query output or did another neat trick to help suppress duplicates. Refer to Suppress Duplicates in Ad-hoc Query Results for ideas.
But let’s get back to the basics. At the simplest level, a query answers four basic questions. These correspond to the four main components of a query: source view, filters, output, and sort/group options. When you answer these four questions in a query, you should have results that match your statement about the records you want to see.
To begin, we can break down our statement to see which parts match each of the four questions. I want to see . . . the names of all constituents who are members and have given a gift this month, listed alphabetically by last name.
The names
This tells us we want to see names in the results. This is the query’s Output.
Of all constituents
This tells us that we need a constituent query, which means we’ll use the Constituents Source View to create the query.
Who are members and have given a gift this month
We want to see members, so we can use the constituency as a Filter. If the constituency is equal to member, we match on the first part. So let’s look at the rest of it. “...and have given a gift this month” makes things a little more complicated. So we’ll need another filter to find the gifts in that time frame.
Listed alphabetically by last name
This tells us how we want to Sort the results.
Now let’s look at each question individually so we can learn about the parts of the query and how we can build it.
The answer to this question lets us know which source view to pick for the query. Based on our statement “I want to see the names of all constituents who are members and have given a gift this month, listed alphabetically by last name,” we can tell we need to use the Constituents source view.
With the Constituents source view, we will create a constituent query. But before we begin to build the query, let’s review the terminology and basic layout of the query screen. In our case, we’ll use the constituent query.
The query screen has four tabs. On the Select filter and output fields tab, you select your parameters. Parameters, criteria, filters all mean the same thing here: these are the pieces of our statement that help us narrow down the results to see only the specific things we want. In our case, that is the names of all constituents who are members and have given a gift this month, listed alphabetically by last name.
All of the fields available in query can be part of your parameters. Field names appear in the center column of the query screen. Under Browse for fields in, you see the field hierarchy that groups similar fields together into expandable nodes. For example, all of the fields associated with constituencies are grouped together under the Constituencies node. When you select Constituencies under Browse for fields in, these fields appear in the center column. Remember, it’s all about fields.
On the query screen, we can also see some filter options and some output options. We’ll talk about the filter options next. And we’ll get to the other tabs a little later!
The purpose of this question is to help determine the filters we’ll need for our query. Which records do we want to include? Do we want to see only constituents who are major giving prospects? Or board members? Or volunteers? Do we want to see only revenue given in the last month or year? In our case, we know we want to see members who have given a gift this month.
To set the filters, we need to know a little more about the filter options. After we select fields for the filters, we sometimes need to make additional decisions. For example, if we use a date field as a filter, we must decide whether we want to use today’s date, a date range, or some other specific date.
Often, we need more than one filter. The filter options allow us to use different filters together. For our example, we need to use a couple of filters to show us members who have given us a gift this month. But to start us off, we need a filter to show constituency equal to member. To find the Constituency field, we need to select Constituencies under Browse for fields in. Then the Constituency field appears in the center column. After we drag the field into the Include records where section, we can select the criteria operator and the values we want to use. So we would say: Constituency is Equal To Member, where “Constituency” is the field, “Equal To” is the criteria operator, and “Member” is the value to use.
What if we wanted to see all members AND all board members? (Although we hope our board members are engaged enough to be members too!) We could select the criteria operator “One Of” instead of “Equal To,” and then pick both “Member” and “Board member.”
We also could have picked other criteria operators, such as “Not Equal To” or “Blank.” You can experiment with them to get a feel for how each one works. For more information, refer to Query Criteria Operators.
But that’s just the first part! We need to get creative with filters and combine them in order to see members who have given a gift this month. Let’s take the “given a gift” part of our statement. We’re going to have to dig a little deeper in the query screen to find this one. For gifts, we’ll need to select Constituents > Revenue > Application Details: Type and set that filter equal to gift.
But we still have one more piece: this month. Now that one isn’t too difficult. That’s just the Constituents > Revenue: Date field. And we’ll need to set that equal to this month!
Here are a few other examples of filters. Can you figure out what these filters mean?
Constituencies\Constituency is equal to Board member
This first part tells us the constituency must equal “Board member.”
Revenue\Application Details\Amount is greater than $10,000.00
The second part tells us the revenue amount must be greater than $10,000.
Did you notice the OR before the second one? That tells us the constituent can meet either criteria and still be included. You might have a board member who has given only $5,000 and you might have a major donor who has given $100,000—both would show up in the results. If we used AND instead of OR to connect the two, only board members who had given more than $10,000 would be included.
Ready for another challenge? What do you think these filters mean?
This one is definitely even more complex. It uses an OR, an AND, and parentheses. See if you pick the right answer!
| □ | The constituents must be board members, regular members, and have given more than $1,000. |
| □ | The constituents must be board members, regular members, or have given more than $1,000. |
| □ | The constituents must be board members or regular members. And either way, they have to have given more than $1,000. |
If you picked the last one, you’re right! The OR between the first two filters tells us the constituent can meet either criteria and still be included—you can be a board member or a regular member. The third one tells us constituents with either constituency must also have given more than $1,000.
Who wants extra credit? Try this one!
Did you notice the difference? This time we changed the position of the parentheses. So this means the constituent would have to be a board member OR the constituent could be a regular member who has given more than $1,000. The results would show all board members, regardless of how much they have given, and any regular members who have given more than $1,000. That’s the power of the parentheses!
I think we’ve played enough with the filters for now, so let’s keep going.
This question provides us with the output fields for our query. Which fields do we want the results to display? For example, if we include phone numbers in the output, do we also want to see if the numbers are marked “Do not call?” The fields we include in the Results fields to display section will help us verify that our results match what we’re trying to accomplish. That means we want to include enough fields for us to tell if we’re really seeing “the names of all constituents who are members and have given a gift this month, listed alphabetically by last name.” So at a minimum, we know we want name as an output field.
This one is pretty straightforward and, in fact, is the default output field for a constituent query! But what if we wanted to see the amount of the gift too? That means we’ll need to also add Constituents > Revenue > Application Details: Amount. Remember, when we see the gift amounts in the output, we're only seeing the gifts that were given this month because of the filter we added.
But what happens if we’re lucky enough for a member to have given us a couple of gifts this month? That constituent would show up twice in our results—once for each gift. So maybe what we really want to see is the total amount of all the gifts each member has given us this month. For that, we need our good friend, the Summarize button. We briefly mentioned the ability to summarize output in the Query Basics section, but let’s take a closer look at how this works.
The Summarize button can do different things depending on the summarize function we select and the field we use it with. The results of the Summarize button are also impacted by the fields in our output.
For our amount field, we can click the Summarize button and use the SUM function to show us a calculation of the field results rather than the actual contents of the field. So what does this really mean? If a member gave us two gifts this month—$50 and $100 (hey, it could happen!)—then we could summarize the amount so the results would include the member only one time, with gift amount combined as $150. Sound interesting? I thought so. So what we need to do is select the amount field in the Result fields to display section, click the Summarize button, and select SUM (Amount).
It’s almost like magic, having the revenue total like that! So let’s keep that as part of our statement now.
A word of caution: if we summarize the revenue amount for constituents but include a date field in the output, all of a sudden the game changes. Since the gift dates are probably different, the output of the two gifts (including date) is now unique and the revenue won’t be summarized. But luckily, we don’t need to include date as an output field since we’re using the date “this month” in our filters.
The Summarize button definitely has a lot of power, so don’t be afraid to use it! For more information about all the functions that are available, refer to Summarize Query Output.
Had enough on output? Let’s move on.
This question provides us with the sort options for our query. Do we want to list constituents in alphabetical order? Do we want to list revenue by amount or by date? Let’s look back at our statement: We wanted to see constituents in alphabetical order. So let’s jump on over to the Set sort and group options tab and see what we can do!
We can sort our query results only by fields included in our output. If you don’t see it under Results fields to display on the first tab, you won’t see it on the Set sort and group options tab. So, under Select results fields for sorting or group filters, we have two choices: Name and SUM(Revenue\Amount). In our statement, we said we wanted to see members, listed alphabetically by last name. Can you figure that out?
That wasn’t too bad. We wanted to sort by name in ascending order, which means A to Z. Coincidentally, that’s the picture on the little button for the options in the Sort records bysection!
Note: Sometimes, we’ll get to the Set sort and group options tab or even the Preview results tab and realize there’s something missing, or we have a new idea about how we want to sort our results. So we might have to go back and add more fields to the output. Don’t worry—it’s not a big deal.
What if, instead of sorting alphabetically by member name, we wanted to sort by the date of the revenue? Guess we’ll have to add the Constituents > Revenue: Date field to the output first. After we do that, we can drag the date field under Sort records by instead of the name field. Now our results would be sorted by date, with the oldest gift (still in this month!) first. But remember, if we include the date field in our output, the SUM(Revenue\Amount) function won’t work to give us the totals.
So we need to think carefully about what’s more important. But keep in mind that queries are a way to group records. We can save the results of the query to use elsewhere in the program or even export the results to a spreadsheet.
But what about that Include records where section? That’s not really like the Sort records by section, is it?
Nope, the Include records where section is a little different. This section is a way to extend the use of a summarized output field. Remember how we used the SUM function a few minutes ago for the output fields on the Select filter and oupt fields tab of our query? This gave us the total amount each member gave this month, instead of having each gift on a separate line.
But what happens if we wanted to summarize the giving, but then also extend that summarized information as yet another filter in our query? What if we wanted to see only the members who gave $100 or more this month? Stick with me here!
For that, we need to use the summarized amount field under Include records where on the Set sort and group options tab too.
Just like with regular filters, when we drag this field to the Include records where section, we get some additional options.
So we can then say that we want the total, summarized amount to be Greater Than Or Equal To $100.
Whew—that got a little crazy there at the end, didn’t it? Let’s pretend that didn’t happen and go back to our nice, easier example. But let’s keep that amount field summarized, like we talked about. That was pretty cool.
Before we check our results, let’s review our original statement (plus the one summarize modification we added, because that was just too cool). We want to see the names of all constituents who are members and have given a gift this month, listed alphabetically by last name. Plus we want to see the amount of their gifts this month summarized instead of seeing each gift separately. Sound good?
Now we can go to the Preview results tab to check to see if things look okay, but . . . wait! The names are sorted alphabetically, but by the whole name, not the last name! So we have Christopher Young before Elizabeth Ashton—not exactly what we were expecting.
Remember how we talked about getting to the end and realizing we need to take a step or two back to add other fields to the output or change the sort order to see exactly what we want? Well, that’s where we are. No big deal.
Let’s jump back to the Select filter and output fields tab and add the last name to the Results fields to display section. We’re looking for the Constituent: Last/Organization/Group/Household name field. That’s a mouthful, but trust me; that’ll get us the last name. Now we also need to go to the Set sort and group options tab to change our sort from the whole name to just the last name. Let’s make those changes and take another look at the Preview results tab.
This looks great! It’s just what we were expecting, which means we built our query correctly! Now we can let our membership director know about each of these loyal and generous members! If she wants to use query results elsewhere in the program, we can create a selection of the results for her. Because the selection is based on the record ID—of the constituents who are members in our case—a mailing process will have access to all the other information about the constituents, such as addresses. So we wouldn’t need to include that information as output in the query. See Create Selections from Queries for more information, including special rules for summarized fields!
Note: If we want to use our query results outside of the program, like in a spreadsheet, we can export the results. However, if we do this, we get exactly what shows up on the Preview results tab. If we summarized revenue, we’ll have only the total and not each individual gift. If we didn’t include a date field, then no date. Check out Export Queries for information on how to export our results.
Now that we have the query squared away, let's give it a name and save it so we can find it again in the future. On the Set save options tab, we can name the query and describe its criteria and output. We can also assign it a category or folder to organize it with similar membership queries. Before we save the query, we can also select to create that selection for the membership director and select whether to allow other users to update the query.
Well, we made it through the tour. Bravo—job well done! We learned a lot about queries and filters along the way, so hopefully you’re ready to dive in and start putting together your own queries!
“I want to see . . . ”