Structuring Queries In Access 2007

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 recognizes 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

Article Source:

This article gives a clear breaksdown of the conventions and operators that can be used in an Access query and why users lean towards RDBMS (relational database management systems) instead of flat files like Excel. With a well produced database architecture in place and knowing about ‘primary keys’ is the key to managing queries and that queries are the heart any database system. Access 2007 makes it even easier to manage!

This entry was posted in Database Theory, MS Access, Queries, Utilities and tagged , , , , , , , , . Bookmark the permalink.

4 Responses to Structuring Queries In Access 2007

  1. benb says:

    Using WordPress is an excellent tool for a blog site + using some basic HTML coding to enhance various widgets always goes a long way to a good look and feel website.
    What in particular were you looking to change for your website?

  2. If you could e-mail me with a few suggestions on just how you made your blog look this excellent, I would be grateful.

  3. CNA Training says:

    CNA Training
    You are a very intelligent person!

  4. Nice site, nice and easy on the eyes and great content too.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.