Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Create a complex query to be used as the source for a grouped report.
Download this lesson’s related exercise files.Services_21_Start Query for Grouped Report.accdb
1.7 MB Services_21_Query for Grouped Report.accdb
Query for Grouped Report
A Calculated Field contains an expression.
Specify criteria to limit the rows of information that are displayed in the results.
If you put criteria under a field, data will only show if the criteria is true. If you have criteria listed under more than one cell, all criteria must be true on the same row for that information to be displayed in the results.
If there is criteria on multiple rows, all criteria on each row must match OR all criteria on the next row, and so on.
The Field cell shows an example of what is going to be in a column of a query. It can be a field from one of the tables that the query uses, or an equation. If the Field cell contains an equation, it must be given a name for the column that is different than any field in the fieldlists that the query uses.
You can specify names for fields and calculated fields by prefacing the expression in a field cell with the column name followed by colon. Do not assign names that are other field names in the source for the query.
IIF is an Immediate IF, and can be used in expressions. The first argument is the condition to test. The second argument is what to return if the condition is true. The third argument is what to return if the condition is false.
IIf( condition-to-test, value-if-true, value-if-false )
NZ is a function to convert Null to a value. NZ stands for Null-to-Zero, but it returns whatever you specify in the optional second argument. This can be a number, or text, or a date (which is really a number).
Nz( variant [, valueifnull] )
Queries can Select data from one or more tables, , sort, and filter. A query that selects information is called a SELECT query. Other types of queries can make batch changes such as updating, appending, and deleting.
The Show row in the query design has a checkbox in each field to check or clear depending if you want that column to show in the results. For instance, you may choose to sort or specify criteria in a column but not show it.
You can specify the fields that a query will sort on. If more than one field is specified, order will go from left to right how they are defined.
Year is a function that returns a variant that is a whole number representing the year of a given date.
Year( date-expression )
Create Blank Query
- To create a new blank query, click the Create ribbon
- Choose Query Design from the Queries group.
- The Show Table dialog box appears. Double-click a table/query to add it, or select one or multiple tables/queries and click Add.
- Click Close to close the dialog box.
- Tables and Queries can also be dragged from the Navigation Pane to the Query Design.
Resize Query Panes
- To resize top and bottom panes in a query, position mouse on the divider line between them.
- When the mouse shape changes to , click and drag.
Add Fields to a Query
- Double-click fields in each list as you want them displayed.
- To put a field into a specific column, drag it to that column. The field you drag on top of, along with any fields to the right will move over
Best-Fit The Columns on the Grid
- You can best-fit the columns on the grid by clicking in the little gray space above each field and dragging across to select them.
- Then move to any of the right borders in the header area that are selected and double-click when the cursor changes to a vertical line and double-headed horizontal arrow.
Open Expression Builder
- To open the Expression Builder, press (Ctrl-F2)
Open Zoom Box
- To open the Zoom Box, right-click and choose Zoom from the shortcut menu
- or press (Shift-F2)
Lesson notes are only available for subscribers.