Complex Query

Association plus other criteria

In this example, let’s find all the people that are members, regular attenders and their city is Chicago.

1. Under the People menu, click Search Profiles. Click on the SEARCH icon.
2. Click on the Associations tab and click over Member and Regular Attender.
3. Click on the Editor tab.
4. Select Address as the table.
5. Select City in the Field drop down.
6. Highlight “is equal to” in the Comparison box.
7. Enter Chicago as the Value.
8. Press QUERY. A list of all the people that meet that criteria are listed.

Multi-Table query

You may need a list, in which the criteria spans several tables. For example, give me all the people who have donated since 2017 who live in Chicago.

1. Under the People menu, click on Search Profiles. Click SEARCH icon.
2. Enter “Contribution” as the Query In table.
3. Select “Donation Date” from the Field Name drop down box.
4. Highlight “is greater than or equal to” in the Comparison box.
5. Enter 1/1/17 as the Value.
6. Press the ADD LINE button.
7. Change the Query In table from “Contributions” to “Addresses”.
8. Select “City” from the Field Name drop down box.
9. Highlight “is equal to” in the Comparison box.
10. Enter “Chicago” in the Value field.
11. Press QUERY. A list of people gave after 1/1/17 a who live in Chicago is returned.

Query in Current Selection

Querying within a current selection allows you to limit your query to only those records that are in the current selection. Let’s say you’ve just done a query of all the people who are Members. Then you want to know who has given in the last 6 months. From the results list, press the SEARCH icon. The query editor is displayed again.

1. Select Contributions as the “Query In” table.
2. Select “Donation Date” from the Field Name drop down box.
3. Highlight “is greater than” from the Comparison box.
4. Enter 1/1/17 in the Value field.
5. Press QUERY CURRENT SELECTION. This will reduce your first list of Members that have given since 1/1/17.

Query with a combination of AND and OR

Some queries require the use of the connector AND as well as the connector OR. When using both And and OR remember to place the OR statements first. An example of this type of query would be, give me a list of all the children or youth who have been baptized. I have Baptized as a custom check box field.

1. Under the People menu, click on Search Profiles. Click the SEARCH icon.
2. Leave People as the Query In table.
3. Select “Family Position” from the Field Name drop down box.
4. Highlight “is equal to” in the Comparison box.
5. Select “Child” as the Value.
6. Press the ADD LINE button.
7. Press the OR button. Notice the AND is changed to OR on the second line.
8. Select “Family Position” from the Field Name drop down box.
9. Highlight “is equal to” from the Comparison box.
10. Select “Youth” as the Value.
11. Press the ADD LINE button.
12. Select “Baptized” from the Field Name drop down box.
13. Enter True Value.
14. Press QUERY. A list of all children and youth who have been Baptized is displayed.

Query using Except

For this example let’s say we want to find all people who live in Byron, but not on Staff. In this example Staff is an Association.

1. Under the People menu, click on Search Profiles. Click the SEARCH icon.
2. Make “Address” the Query In table.
3. Select “City” from the Field Name drop down box.
4. Highlight “is equal to” from the Comparison box.
5. Enter Byron.
6. Click the ADD LINE BUTTON.
7. Click the EXCEPT button. The AND changes to EXCEPT.

8. Go back to Query In People and select “Association” from the Field Name drop down box.
9. Highlight “is equal to” from the Comparison box.
10. Select “Staff” as the Value.
11. Press QUERY. A list of people that aren’t on staff, who live in Byron is displayed.

Comparing two result sets

There are several ways that you can compare two sets:

An intersection compares the results of two queries and returns the records that were common to both sets. For example, I want a list of all the people who have donated and are enrolled in a group.

A union combines the results of two queries. An example of the union is – give me a list of people who either donated or joined a group.

A difference would subtract out the records that were common to both sets. An example of the difference is – give me all the people who donated but are not in a group.

For these examples, let’s look at all the people in our database that have donated in the last year. Then let’s compare that to the list of people that belong to a small group.

For the first query:

1. Under the People menu, click on Search Profiles. Click the SEARCH icon.
2. Change the Query In table to “Contributions”.
3. Select “Donation Date” from the Field Name drop down box.
4. Highlight “is greater than or equal to” from the Comparison box.
5. Enter the earliest date of donations to consider, for example 1/1/17.
6. Press QUERY. A list of people who gave to the church on or after 1/1/17 is displayed.

From the results list, press the COMPARE SETS button. This brings up the screen that allows you to perform the intersections, unions and differences. In the left box labeled Set A, mark the button for Current Selection then press the button GET SET A. Now SET A contains all the people who have donated since 1/1/14.

Next, let’s move over to the box labeled Set B. Click on the button for Query Editor, and press the button GET SET B. The query editor is displayed.

1. Change the Query In table to Group Members.
2. Select “Inactive” from the Field Name drop down box.
3. Highlight “is equal to” in the Comparison box.
4. Choose False as the Value.
5. Press QUERY. The active people who are in a group are in SET B.

Click the button to Combine Sets, if you’d like to see the “union” of the two sets. That is, the combination of the people who gave and the people who are in a group. Press the OK button to see the results of the intersection.

Click Subtract B from A, if you’d like to see the “difference” between the two sets. This would show you all the people that gave but are not enrolled in a group. Press OK to see the results of this difference.

Click Subtract A from B, if you’d like to see the “difference” between the two sets. This difference would give you the people that are in a group, but have not donated. Press OK to see the results of this difference.

Click the button to Common to A and B, if you’d like to see the “intersection” of the two sets. That is, the people that have both donated and belong to a group. Press the OK button to see the results of the intersection.