Overview of Queries
Queries filter the data so that you see just the records you want to see. For example, you could use a query to see only active clients from the Names database. Or you could use a query to see all matters opened prior to 2010. Queries can be used on-screen to view certain records or with reports to print just the information you want.
Click here for some examples of queries.
Types of Queries
There are three different types of queries:
-
Regular: Has all the settings to create a query.
-
Quick: A simpler version of the regular query. Has limited settings to create a query.
-
My Queries (Mine): A regular or quick query that has been tagged as "mine". Simply another level of categorization that lets you access commonly used or favorite queries quickly.
Query Expressions
Queries are made up of query expressions. Query expressions are conditions you ask of the data. For example the query expression:
"Opened = 1/14/10"
applied to matters asks to display all matters opened on January 14, 2010.
One or more query expressions form a query. If you use more than one query expression, you must connect the expressions with "and" or "or".
NOTE: Parentheses can be used to group query expressions, like this: (Opened = 1/14/02). Use of parentheses is optional. Although they might make it easier for you to read, they won't change the outcome of the query.
And / Or Connectors
When you have more than one query expression in a query, you connect them with "And" or "Or".
And statements ask if two or more query expressions are true. For example, if you needed to find all matters opened after 2010 for client Joe Smith, you might write a query using "and" like this:
Opened greater than or = to 2010
and
Last = Smith
Or statements ask if any query expressions in the query are true. For example, if you needed to find all matters opened after 2010 plus all matters for client Joe Smith, you might write a query using "or" like this:
Opened greater than or = to 2010
or
Last = Smith
When you add a new expression to an existing query, a window asks if you want to combine the new expression with AND or OR. Always think of what ONE record in the database has to have to pass the test. If the first expression must be true AND the second expression must be true for one record, select AND. If a record only needs to match ONE of the criteria, select OR.
Query Optimization
Abacus uses state-of-the-art query optimization to make queries faster than normal - in some cases 1,000 times faster. Most users can ignore this, but "power users" may want to take special advantage of it. Basically, whenever a query is set Abacus scans the indexes (in an instant) to see if the query involves the fields used in the index. If so, the index can instantly identify a range of records for the query, and Abacus doesn't have to look at the database records at all to determine which records to show.
For instance, Abacus maintains an index for names on ID. If a query has "ID = 99", Abacus instantly knows the range of records that start with "99" from the index, and it doesn't need to look at the database to determine if a record is a "hit". If all the clauses in an index expression are "optimizable" in this way, there is no delay at all in finding records!
If, however, a clause in a query references a field that is not indexed, then Abacus must look at database records to determine if the record matches the query. This can take time if none of the clauses are optimizable. So, if you have queries that you use all the time but seem to take too long to find records, consider building a user-defined index on the fields you are testing. For example, if you often generate names reports with queries on the Class field, create an index on Class.
Click here for more information about user-defined indexes.