Build a Query

Return to Table of Contents

Whether you are printing labels, printing statements, or globally assessing members, the query is the way in which you communicate with your computer which members to select from the database.

The basic query screen looks as follows:

On this screen, all of the categories you can query by are organized into the folders shown above. Whichever category you are going to query by, such as Age, you would look for it under one of these folders and click it so that it appears in the query field.

Before beginning to build your query, it is a good idea to do two things:

1. Decide which members of the congregation will be included in this process (printing labels, printing statements, global assessments, etc.).
2. Determine how those members are represented in the system - how are their membership screens different from everyone else's? How are they differentiated during the data entry process?

For example, if you want to print labels for all single women over 30 - these women are represented in the system by having "F" in the Sex field, "S", "W", or "D" in the Marital Status field, and a Birthdate prior to 1969 (or the appropriate year). These fields all happen to be Membership fields (as opposed to Participation or Checklist fields) so you would click on the plus sign to the left of the Membership folder to open it. To query by a particular field, highlight the field and click so that it appears in the Query Field box. Then we set the field equal to some value (or greater than, etc.) using the Operation and Value fields. Our first example would be Sex = F so we select the "=" from Operation and type in the Value as it appears on the membership screens for this query, an "F." For most queries, the join will be AND so we would select that here. Click <Add> to view the line of query on the right-hand side of the screen.

For the second line of query, to make it easy on ourselves, we will enter "Marital Status <> M" which means Marital Status is not equal to M. Since there are only four possibilities for Marital Status in TempleTracker (S,M,W,D) and we want all but one, the <> sign works best in this case.

For the Birthdate, we will select Birthdate(mm/dd/yyyy) < 01/01/1969. For date fields, the format of the Value must match the format of the field as shown in parentheses. This line of query will pull everyone born before Jan. 1, 1969, or all those over 30. Clicking <Add> after each line of query is entered results in a screen that looks like the one below:

At this point, we would click <Save> and proceed with printing statements or labels, or with global assessments. The system would scroll through the records and pull out only those which met all of these records SIMULTANEOUSLY (because we used AND as the join). Another way to look at it is that the computer limits the database by the first line of query so that the subset includes all women. Then it looks through all of them and finds anyone without an M in Marital Status. Now the subset is smaller. Next it searches this smaller subset for anyone over 30. The system then performs the action requested (printing labels, statements, etc.) for the resulting subset.

Another example of a query might be printing statements only for members who owe money. This isn't specifically represented in the database while performing data entry but can be calculated by the system if you use one of the Financial Query Fields. In this case, we would use the field "Total Amount Due." Set the Operation as ">" and the Value as 1 to print statements for people who owe more than $1.00. Click <Add> to move the line of query to the right side of the screen. Your query would look as follows:

Another typical query would be "All Heads of Household" if you are printing labels or a membership list. Head of Household is designated in TempleTracker by Member Code of A. To enter this query, click the plus sign next to the Membership folder and click Member Code so that it appears in the Query Field box. Set the Operation to = and the Value to A. Use a Join of AND and click <Add> to move the line of query to the right hand side of the screen. Click <Save> and proceed with label printing, etc.

Operations used in Queries:

 

=

Equal

>=

Greater than or equal to

<=

Less than or equal to

<>

Not equal to

<

Less than

>

Greater than

LIKE

Use LIKE with wildcard * - for example, Name LIKE "*/S* would pull everyone with a last name beginning with S. The "/S" is found somewhere in the field. (The slash is used to designate the S is the first letter in the last name, rather than anywhere in the name field.)