ODB has a fairly compact screen for querying the database which is very easy to use for simple queries. For complex queries involving many different criteria, a bit of training is usually needed, as the queries often must be written using SQL (known as structured query language). Please note that the query engine will work best if you run the conversion utility that is supplied with version 0.8.9 of ODB, and are running the most current version of ODB. This utility removes most ‘null values’ from your database, values that make your queries more difficult to write.

I. Basic Concepts
The select records screen is divided into two areas as shown below:

___________________________________
| checkboxes and pull downs |
| |
___________________________________
| |
| query results area |
|___________________________________|

The elements in the top half of the screen are all restrictions on the data you want to select. This means that if you adjust more than one element, the result will be the "intersection" of two sets of results. E.g. if you click on the member and voter checkboxes, you will get a list of people who are paid members in your organization and are also registered voters. Or if you choose Groups with an area served of "National" and bad address, you get a list of groups identified as national where the address is marked as bad.

II. Broadening a query
You might want to do the opposite, however. If your organization only has 20 paid members and if your database only has 20 registered voters, and there are only 5 people who are in both of these categories, then you might want to look at everyone (the 35 people in either group) as opposed to the five people who are in both groups. The "View Custom Query" feature of ODB enables you to accomplish this goal.

You can try this yourself, using this example -- clicking the member and voter checkboxes. Once you have done this, try clicking on the "View Custom Query" checkbox as well. Assuming today’s date is July 12, 2002, the bottom half of the screen will show you your database search query so far: select * from People Where MemExpire > #7/12/2002# AND Voter = true

Since ODB is kind enough to show you the underlying SQL, you won’t have to create your query from scratch. Most of what you need to do can be done with your mouse, using "copy" and "paste"!

Let’s convert this "intersection" query to the opposite kind (known as a "union") so that we can look at all 35 people who are either members or voters:

1. First, check the box that says &"Add SQL to broaden your search". This will open up a box where you can move part of your original query


2. Type one-half of the original query into this box. For example, you could simply type "voter = true" into this box. Or you could copy this text from the Show SQL box and then paste it into the Add SQL to Broaden box. To copy information, select the text with your mouse and use the Copy option in the menu that you will see after pressing the right-mouse button on your mouse.


3. Now uncheck the "voter" box near the top of the search window, and then click "Search"


4. You are done! Not only have you found the people you wanted, but the actual query language is now displayed. It should say:

select * from People where (MemExpire > #7/12/2002# or voter=true)

The key difference between this and what you had before is that the "and" has been replaced with an "or."

III. Restricting a query
If you play around with this some more, you could even organize a voter registration campaign. Say you had 300 members and knew how to find the 100 members were registered to vote (because this is explained on the previous page!). But say you wanted to find the 200 members who were not registered, and then print out phone calling sheets so that you could organize a phone bank. First you will need to move what you have put into the "Add SQL to broaden your search" box into the "Add SQL to further restrict your search" box. This will ensure that you will get no more than the 300 members you started with by checking the "Member" box near the top of the screen.

However the "restrict" box restricts by including a part of what you already have, and the part you are selecting using "voter = true" is the group of people who are already registered to vote. That’s not what we want, so all we need to do is change this to:

Voter = false

Or you could use:

Not voter = true

Which is exactly the same thing!

IV. Choosing multiple states
Some other things you could try in the "Add SQL to further restrict your search" box:

state="MD"

This will restrict whatever else you have chosen above to people in Maryland.

state="MD" or state="TX"

This will allow you to select records in Maryland or Texas. However, this is not the best way to search for records in two states, as SQL likes it if you add parentheses to control the order in which the various operations in the query are performed. So the query:

(state="MD" or state="TX")

will be sure to restict whatever else you might have checked above to the two states of Maryland and Texas, where if you left the parentheses out only Maryland would have been a "restriction" and you might end up with way too many people who live in Texas (or at least more than you intended).

V. More complex searches.
ODB lets you search on any field that is in the database. For example, you could use:

line2 like "*naacp*"

This will find people who have the words "naacp" in the second line of the address.

(memexpire > #6/1/02# or Activit like "*C1*" or Activit like "*S2*") and (not mailcode like 'A*' or isnull(mailcode))

This will find people who expire after June 1, as well as people with the activity codes C1 and S2. However, people who have addresses marked as bad will be excluded.

(memexpire > #6/13/02# or constit like '*R2*')

This will find people who expire after June 13 and will also INCLUDE people who have a constituency code "R2" (which was in this case the Rotary Club).

isnull(joined) and comments like "*attended 2002 concert*"

This will find people who never joined and who are marked as having attended your ‘2002 concert’

isnull(joined) and comments like "*attended 2002 concert*" and (not mailcode like 'A*' or isnull(mailcode))

Finds the same people and excludes addresses marked as bad. Finally,

isnull(memexpire) and (Activit like "*R2*" or Constit like "*S2*") and (not mailcode like "A*" or isnull(mailcode)) and not (comments like "*attended 2002 concert*")

will pull up all the people who never were members and had EITHER the activity code of "R2" or the Consitutuency code of "S2", but did not have their addresses marked as bad.

VI. Saving Queries
The final thing worth mentioning about ODB queries in this brief tutorial is that you can preserve them for future use. If you will notice, there is now a Save Query… button as well as a Load Query… button in the area of ODB next to the "Add SQL to further restrict your search" check box. We encourage you to try this out: currently ODB will save its queries within the queries folder, located inside of the folder where you originally installed the ODB software.