Creating Queries in Access 2010

6/15/2010

Contents

Introducing Operators

There are several types of database objects and tools you need to use when you create a criterion to narrow the records returned by a query or to calculate a value. The first set of objects to which you need to refer includes database tables and their fields. For example, to calculate the subtotal of a line in the Northwind sample database’s Order Details table, multiply the Unit Price by the Quantity ordered, and adjust the total if the customer gets a discount (as noted in the Discount field). The expression to perform the first part of that calculation is [Order Details]![UnitPrice]*[Order Details]![Quantity].

Note that table fields are called out with the name of the table enclosed in square brackets, an exclamation point, and then the name of the field in square brackets.

Arithmetic Operators

Operator	Description
-	Subtraction (6-4=2)
*	Multiplication (6*4=24)
/	Division (6/4=1.5)
\	Integer division (6\4=1)
+	Addition (6+4=10)
Mod	Modular division (6 Mod 4=2)

Comparison Operators

Operator	Description
<	Less than
<=	Less than or equal to
<>	Not equal to
=	Equal to
>=	Greater than or equal to
>	Greater than
Between “Value1” And “Value2”	Between two values, inclusive (for example, Between “1” And “3” would return “1, 2, 3”)

Logical Operators

Operator	Description
AND	Both elements of an expression must be true.
NOT	The expression must evaluate as false.
OR	At least one element of an expression must be true.
XOR	Exactly one element of an expression must be true, not both.