- By Andrew Couch
- Selecting all columns from a table
- Selecting individual columns from one or more tables
- Joining tables to see unmatched or missing records
- Filtering by single and multiple combinations of choices
- Adding calculations with the expression builder
- Returning the top matched records
- Eliminating duplicate values
- Creating a summary calculation
- Prompting to filter data with parameters
- Creating a crosstab query with the Query Wizard
- Simplifying a problem with a query by using other queries
- Adding two sets of query results together
- Resolving ambiguous outer joins
- Creating an additional query to resolve a problem with mixed joins
Adding calculations with the expression builder
You can type calculations in queries directly into the query, both to create a column and as criteria filtering against an existing column. To avoid mistakes when referring to field names or to explore the myriad built-in functions, you can use the expression builder to provide assistance when creating more complex expressions.
Access provides a wide range of built-in functions to help you create expressions. Two particularly popular functions are the NZ function, which converts a NULL to a value such as 0 for a number field or to an empty string for a text field, and the IIF function, which allows you to conditionally perform calculations. You will also find that the Zoom box is particularly useful when you are working with complex expressions.
Create an expression with the expression builder
Click a blank column heading. Make sure that you have already saved your query; otherwise, you will not see the column names in the expression builder.
Click Builder (or right-click and select Build).
In the Expression Builder dialog box, double-click the fields in the Expression Categories pane to add them to the expression.
Enter the functions between each field (such as *, +, -, and so on).
Click OK. The field expression will be read as Expr1:[Quantity]*[UnitPrice].
Click in the field, and press Shift+F2 (or right-click and select Zoom).
Edit the field title, and click OK.