Suggestions for designing queries
As you become more proficient at writing queries, you will most likely find your own favorite methods and best practices. Following are some suggestions you can follow singly or in combination with one another that can help you achieve good results.
Suggestion #1: Plan your Query
Suggestion #2: Design a Query to find the correct values in a column
Suggestion #3: Add Query elements one at a time
Suggestion #4: Check the integrity of the results if the Query contains conditions
Suggestion #5: Save Queries often and always
Suggestion #6: Delete unused and experimental Queries
Suggestion #7: Re-use and modify existing Queries
Suggestion #8: Use tags to organize your Queries
Suggestion #1: Plan your Query
Before you even begin to build a query in ParishSOFT IQ, do the following:
-
Decide exactly what you want to know.
A well-designed query starts with having a good idea about what you want to find out and where to find it. One way to start is by formulating a question. Ask yourself what information you need to obtain from your database. Building a query is more complicated than asking a question, but knowing the question you want to answer is the best way to start. You can also make a statement or list specifically what you want to know. Alternatively, as illustrated below, you drawing a diagram can help you pinpoint the exact information you want to know.
Example
Suppose you want to know which members contributed more than $500.00 to the All Souls fund.
- You can turn this idea into a question: What are the names of members who contributed more than $500.00 to the All Souls fund?
- You can make a list:
- You can draw a diagram to illustrate what you are trying to find out:
-
Identify the information you need to answer your question.
In this step, analyze your question to determine what information you need and where that information resides in your database. Use the following questions as a guide:
-
Which groups or database entities contain the data you are looking for?
-
Which specific columns hold the data you need?
-
What level of detail do you need? Do you need to limit the results of the query—based on specific values? based on a period of time? based on a specific group or organization? In other words, what conditions, if any, must you apply to the data to obtain the results that you need?
Make a list of your information needs or draw a diagram that illustrates your exact requirements:
Suggestion #2: Add a column to a Query to find correct values
If you are setting up a condition on a column and you need to know the column's exact values, simply add the column to the query and then run the query to see the values in the column. This way, when you add the condition you know what the exact value is. This technique is particularly helpful when you are not sure of the spelling or formatting of a value, or even if the value exists. The following examples show you how to apply this suggestion.
Example 1
Suppose you are working on a query in which you want to limit the records to a particular Religious Education school year. However, when you go to apply the condition, you realize that you do not know what the exact values are in the Religious Education School Year column or how they are represented. For example, is a school year represented with no spaces, like this: 2014-2015? Or, is it represented with spaces, like this: 2014 - 2015?
To find out, simply add the column and run the query. The exact values for the column are displayed in the Query Results panel. When you set up the condition, you can enter the value correctly in the condition's text field.
Example 2
Suppose your query requires you to specify the exact name of a fund as a condition. However, you are not sure if the name is "Bishops Oversea Appeal" or "Bishops Overseas Appeal". To solve this dilemma, add the Funds → Description column to the query and then run it so that you can see the exact values contained in the column.
-
Select Funds → Description.
-
Drag the Description column into the Result Columns panel, making sure you place the column on top of the [Add new column] link. Release the mouse button.
-
Slide your mouse to the right and click
. Select Ascending from the Sorting menu so that the results display in A to Z order.
-
Click Execute Query to run the query.
-
The Query Results panel shows the exact values in the Description column.
The results show the exact name of the fund is "Bishops Overseas Appeal". You can now type the name correctly in the text field when you apply the condition to the Description column.
-
Suggestion #3: Add Query elements one at a time
When designing a query, it helps to follow a specific sequence of steps. We recommend that you add elements to your query one at a time and run the query after each addition or modification. Then, look at the results of the query after you run it each time to both understand the data and to make sure that the results you obtain match your expectations.
Example:
Suppose that you want the names of active members between the ages of 45 and 65 who live in the 48108 zip code. The following example shows the sequence of steps you can use to put Suggestion #2 into practice.
Sequence of Steps for Building a Query That Shows Last Names of Active Members Between 45 and 65 Living in the 48108 Zip Code
At this point, your results set in the Query Results panel has four columns of data: Members Last Name, Members Age, Members is Active, and Members_Contact Info Home Zip
a. Because you want to limit your results to members aged 45 and 65, add the following condition to the Members Age column: Members Age is between 45 and 65. Click b. Review the results set in the Query Results panel. You should now see only the names of members in the 45 to 65 age range. c. Disable the condition on the Members Age column. Then, re-run the query without it. (A detailed explanation of this step is provided under Suggestion #4.) d. Because you want active members only, add the following condition to the Members is Active column: Members is Active is equal to 1. e. Review the results set in the Query Results panel. You should now see only the names of active members. f. Disable the condition on the Members is Active column. Then, re-run the query without it. (A detailed explanation of this step is provided under Suggestion #4.) g. Because you are limiting the members to those living in the 48108 zip code, add the following condition to the Members.Contact Info Home Zip column: Members.Contact Info Home Zip is equal to 48108. Click h. Review the results set in the Query Results panel. You should now see only the names of members who live in the 48108 zip code. i. Enable all of the conditions. Re-run the query. (A detailed explanation of this step is provided under Suggestion #4.) The results set for the completed query is displayed in the Query Results panel. You should see only the names of active members who are between the ages of 45 and 65 and who live in the 48108 zip code. Verify this by reviewing the data in the panel:
|
Suggestion #4: Check the integrity of the results if the Query contains conditions
If your query contains multiple conditions, use this process to verify that each condition is producing the desired results.
By default, when you add a condition to a query, it is enabled.
- Add the first condition.
- Run the query. Then, check the results with the one condition enabled. Verify that the records in the result set are as expected.
- Click
(the Toggle enable button) to disable the first condition.
This button is located on the far-right in each condition row. The button may or may not be immediately visible. If visible, it is shaded. If the button is not visible, slide your cursor all the way to the end of the row the condition is in until you see the button, and then click it.
- Run the query again. Then, check the results obtained when the first condition is turned off.
- Add the next condition.
- Run the query again. Then, check the results obtained with the second condition enabled. Verify that the records in the result set are as expected.
- Click
- Run the query again. Then, check the results obtained with the condition enabled. Verify that the records in the result set are as expected.
- Repeat Steps 5 - 8 for each remaining condition.
- Enable all conditions by clicking this button for each condition:
.
- Run the query. Confirm that the results set contains the expected records now that all conditions are applied.
Suggestion #5: Save Queries often and always
Following are a couple of important reasons why you should save your queries often:
● After 30 minutes of inactivity, ParishSOFT IQ times out and forces you to log back in. To prevent loss of your work, save queries you are working on often because you will lose any unsaved work if the system times out.
● Sometimes you may design a query or make changes to an existing query and those changes generate a result set that does not exactly meet your needs at the time. It is a good idea to get in the habit of saving your queries—even those you consider experimental—because you never know whether a query you create today might end up providing the information you need later. If you do not save a query and then find out later that you need the information, you may not remember exactly how you designed it and end up spending more time replicating it from memory it to get the desired results.
If you are not sure whether or not you should save a query, save it. Be sure to give the query a useful description so you remember its purpose. This recommendation includes queries you create when first learning how to use ParishSOFT IQ. You can always delete unused queries later.
Suggestion #6: Delete unused and experimental Queries
It is important to manage the number queries you have in your system to minimize clutter. From time-to-time, review the queries saved in your My Queries list. Delete redundant queries and any queries you are no longer using. For instructions on deleting queries, go to How to delete a query.
Suggestion #7: Re-use and Modify Existing Queries
Saving queries and then using them as a basis for new queries is a strategy that can save you lots of time and effort. Instead of writing a new query from scratch, consider creating new queries by re-using common elements in a saved query. Open the saved query, make adjustments to it to satisfy your new information requirements, run it, and then save it under a new name.
This technique is especially time effective when you have a variety of census-related questions. To efficiently get answers to those questions, you could design a base query that contains common data fields (for example, Family DUID, family last names, mailing addresses, and phone numbers). You could then save the query and then re-use it as a source to design special queries that get at specific information. Using this technique not only saves time but also ensures that each of the special queries is built on the same base of census data.
Suggestion #8: Use tags to organize your Queries
A query tag is a user-defined label that describes or categorizes a query. As long as it is meaningful to you, the label can be a single word or phrase that identifies the type of query or its purpose.
Tagging your queries is optional, but having a tagging system is useful especially if you have a lot of queries. One benefit of using tags is that they are searchable elements. You can quickly find a query you are looking for if it is assigned a tag. Tags can be used to identify various collections of queries. So, if you devise a tagging scheme that you consistently apply when saving your queries, you can use the tag as a filter to quickly locate all of the queries in your system that share the same tag. For instructions on creating tags, go to How to create and manage query tags.
Related Articles
How to create and manage query tags
Updated