Introduction To Queries

Aegis CRM’s Query module is designed to help you get the answers you need from your database quickly and accurately. It is a very powerful tool that can be used to find the answers to simple questions like:

"How many of our donors have given a gift of $100 or more in the past twelve months and live in California?"

You can also use the Query module to create complex queries that answer questions such as:

"We need to know how many of our donors who received our March Announcement Letter have given at least three gifts in the past six months that totaled more than $1000, or were added to the system in the last three months and have given at least $100."

For either example you use the same process to build your query. With an understanding of the basic principles of queries and how they interact with your database, you can provide answers to any database question.

For any query, you need to answer two questions:

What is the criteria for identifying common records?
What type of records and information do you want in your results?

Criteria

The criteria you use filters the database to separate the records you want from other records in the database. There are many different ways to apply these filters, depending on the type of query you create. When you select a query type, you are applying the first filter to your query.

Query Types

Aegis CRM includes multiple query types you can use to identify the information you want included in your results.

Example: You have a partner named Joe Smith who has given three gifts in the current year. If you create a contact query for current year donors, Joe Smith is included once in the results. If you create a gift query for gifts received during the current year, Joe Smith will be included in your results for each gift he made.

The most commonly used query types in Aegis CRM are Contacts, Activity, and Gifts. The following table includes a description and sample criteria for each query type.

Query Type	Description	Sample
Contact Query	Group partners that have something in common.	Partners who live in Maine with an average gift of $25 or higher
Activity Query	Group records based on inbound activities including any that did not include a donation.	Partners who responded to a specific appeal, during a specific time frame, and made a pledge
Gift Query	Group transaction records based on something they have in common.	Gifts given by specific appeal, during a specific time frame, and entered in a specific batch.

Operators

You can operators in your query to help identify the records you want in your results. The And operator looks for records that meet all criteria you add to the query.

Example: How many of our partners have given a gift of $100 or more in the past twelve months and live in California?

The results include records for all partners who gave $100 or more living in California.

The Or operator looks for records that meet at least one of the criteria you added to your query.

Example: How many of our partners have given a gift of $100 or more in the past twelve months or live in California?

You will receive more results because they include all records for partners who have given $100 or more regardless of where they live and records for all partners living in California regardless of the amount they have given to your organization.

Value operators specify what to include in the query results. In the example query above, the phrase "have given a gift of $100 or more" uses the value operator "equal to or greater than" to determine which records meet that criteria and should be included in the results.

Note: Make sure that when you create queires using multiple criteria, you identify the value operators for each criteria you use.