People prefer to keep complex data sets in databases rather than in flat files because data can be found and grouped more precisely when it is stored in a database. The ability to draw data out of a database depends upon the ability to structure queries, or requests that define specific records. Structuring queries in Access 2007 is relatively easy because Access is designed to handle many different types of operators.

An operator is a special symbol or reserved word that triggers the database to act in a certain way. Access 2007 recognises several types of operators, including comparison operators, arithmetic operators, logical operators and special expressions such as like, between..and, in, and is null. You may also use combinations of these types of operators when structuring queries in Access 2007.

Comparison operators ask Access 2007 to perform a comparison between two values. In a query, a comparison operator can locate all records where a field value is greater than, less than, equal to, not equal to, greater than or equal to, or less than or equal to a specified value. Comparison operators can be applied to all fields that contain a literal value. A literal value is a value that has been directly entered into the field. Access recognizes four literals: text, numbers, dates and times, and constants.

Arithmetic operators are basic mathematical operations. Access recognizes addition, subtraction, multiplication, real and integer division, exponentiation, modulus and unary operators. These operators are used to build formulae and other mathematical expressions. If you are calculating the total value of a particular group of records, or creating an invoice, you would use these operators.

The “like” operator can be used to identify less specific search criteria. The like operator is used with a wildcard such as ? or *. The ? wildcard substitutes a single character. The *wildcard identifies a string of characters. Using the query “like a?c”, Access would produce records that contained aac, abc, acc, adc and so on in the search field. Using the query “like a*c”, Access would produce records that contained acidic, amoebic, anaerobic, auric or any other field value of any length that started with “a” and ended with “c” and was contained within the search field. Access also recognises “between…and” for identifying a range of values. In can be used to limit a range of values, and is null can be used to search for blank or empty fields.

Access 2007 understands logical operators such as and, or, not, xor, eqv and imp. The and operation returns a true value only when both conditions are true. Or returns a true value when either expression is true. Not negates the expression. Xor is an exclusive or. It returns a true value when either expression – but not both – are true or false. Eqv returns a true value when both expressions are equal and Imp returns a false value if the first expression is true and the second expression is false.

Notes for editors: Claire Blinman is the training manager at Computer Training Solutions in Bristol. Computer Training Solutions offer Excel training or call 0800 019 6882