Complex Query for Grouped Report

Locked lesson.

Upgrade

About this lesson

Create a complex query to be used as the source for a grouped report.

Quick reference

Query for Grouped Report

Application Terminology

Calculated Field

A Calculated Field contains an expression.

Example:
CalcAmt: [WSRate]*Nz([Qty],1)*IIf([WorkServices].[TiTyID]>1,[WSTime],1)

Criteria

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.

Field

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.

Field Alias

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.

For instance:
MyNewFieldname: expression

IIF Function

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.

Syntax:
IIf( condition-to-test, value-if-true, value-if-false )

NZ Function

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).

Syntax:
Nz( variant [, valueifnull] )

Query

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.

Show

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.

Sort

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 Function

Year is a function that returns a variant that is a whole number representing the year of a given date.

Syntax:
Year( date-expression )

Steps

Create Blank Query

  1. To create a new blank query, click the Create ribbon
  2. Choose Query Design from the Queries group.
  3. The Show Table dialog box appears.  Double-click a table/query to add it, or select one or multiple tables/queries and click Add. 
  4. Click Close to close the dialog box.
  5. Tables and Queries can also be dragged from the Navigation Pane to the Query Design.

Resize Query Panes

  1. To resize top and bottom panes in a query, position mouse on the divider line between them.
  2. When the mouse shape changes to , click and drag.

Add Fields to a Query

  1. Double-click fields in each list as you want them displayed.
  2. 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

  1. 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.
  2. 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

  1. To open the Zoom Box, right-click and choose Zoom from the shortcut menu
  2. or press   (Shift-F2)
Login to download
  • 00:05 This is lesson 21 of Access 2013.
  • 00:09 You will learn how to make a complex query
  • 00:12 that will be used as the source for a grouped report.
  • 00:16 Hi, this is Crystal.
  • 00:18 Queries can gather information from more than one table,
  • 00:21 sort, and filter.
  • 00:23 Queries can do more than what most people use them for.
  • 00:27 Queries can Select data from one or more tables.
  • 00:30 They can also make batch changes
  • 00:33 such as updating, appending, and deleting.
  • 00:37 To create a new blank query, click the Create ribbon
  • 00:40 and choose Query Design from the Queries group.
  • 00:44 The Show Table dialog box appears, which I usually close.
  • 00:49 Drag tables from the Navigation Pane to the Query Design.
  • 00:53 The first table we will drag in is Customers.
  • 00:56 You can also select multiple tables and drag a selection.
  • 01:01 Work contains information about when work was done,
  • 01:04 and Work Services tracks the individual tasks.
  • 01:08 The Services table has the service name that corresponds
  • 01:11 to the ServiceID stored in the Work Services table.
  • 01:16 ServTypes is for categorizing Services.
  • 01:19 Because relationships are defined between all tables,
  • 01:22 Access shows them.
  • 01:25 Maximize the query window within Access
  • 01:27 by double-clicking its title bar.
  • 01:30 Stretch the fieldlists to show all the fields
  • 01:33 you might want to pick.
  • 01:35 The tracking fields at the bottom will not be chosen
  • 01:37 so they don't need to show.
  • 01:40 Give more space to the top pane
  • 01:42 by positioning the mouse on the divider line
  • 01:45 between the top and bottom panes.
  • 01:47 When the mouse cursor changes to a horizontal line
  • 01:49 and double-headed vertical arrow, click and drag.
  • 01:53 Double-click fields in each list as you want them displayed.
  • 01:57 To put a field into a specific column, drag it to that column.
  • 02:01 The field you drag on top of,
  • 02:03 along with any fields to the right, will move over.
  • 02:07 You can best-fit the columns on the grid
  • 02:09 by clicking in the little gray space above each field
  • 02:12 and dragging across to select them.
  • 02:15 Then move to any of the right borders in the header area
  • 02:18 that are selected, and double-click when the cursor changes
  • 02:21 to a vertical line and double-headed horizontal arrow.
  • 02:26 You can add fields to the grid multiple times.
  • 02:29 I want to calculate the amount, which is the
  • 02:32 work service rate times the quantity.
  • 02:35 Press Shift-F2 to pop up the Zoom box,
  • 02:38 and then click on the Font command button
  • 02:40 to make the text bigger so it is easier to see.
  • 02:44 Move the Zoom box by dragging its title bar.
  • 02:47 Go to the beginning of the equation by pressing the Home key,
  • 02:50 and type the calculated fieldname followed by a colon.
  • 02:54 Call this field CalcAmt.
  • 02:57 The calculated amount will be the Rate times the Quantity.
  • 03:01 Click on the Datasheet View icon to see the results.
  • 03:04 When quantity is not filled out, the amount is not calculated.
  • 03:09 In Access, when you multiply nothing by anything,
  • 03:12 you get nothing, which is called Null.
  • 03:16 Go back to the design view and wrap the
  • 03:18 reference to quantity with NZ.
  • 03:21 NZ is a function to convert Null to a value.
  • 03:25 NZ stands for Null-to-Zero,
  • 03:28 but it returns whatever you specify
  • 03:30 in the optional second argument.
  • 03:33 This can be a number, or text,
  • 03:35 or a date, which is really a number.
  • 03:37 Quantity should default to 1.
  • 03:39 Now when we look at the results,
  • 03:41 amount is calculated even when quantity is null.
  • 03:46 The calculated amount does not take into account wsTime,
  • 03:50 which is the time actually spent.
  • 03:53 Depending on how this item is billed,
  • 03:55 this value may have no bearing on billing.
  • 03:58 Time is sometimes recorded for internal use, not billing.
  • 04:03 Go back to design view and add TiTyID to the grid.
  • 04:07 This is the time type and specifies if an item
  • 04:10 is a fixed price, or depends on the time to get the amount.
  • 04:14 Mowing lawns is a fixed price
  • 04:16 that depends on the size of the lawn.
  • 04:19 Trimming bushes and emptying bags of grass
  • 04:21 is based on quantity.
  • 04:24 To have Access help us construct rest of the equation,
  • 04:28 press Ctrl-F2 for the Expression Builder.
  • 04:31 To make the font bigger in the expression box,
  • 04:33 use Ctrl-mousewheel.
  • 04:35 If the time type is greater than 1,
  • 04:38 the amount of time needs to be considered.
  • 04:41 IIF is an Immediate IF, and can be used in expressions.
  • 04:46 The first argument is the condititon to test.
  • 04:49 The second argument is what to to return
  • 04:51 if the condition is true.
  • 04:54 If the time time is a rate, then the calculated amount
  • 04:57 will be multiplied by the time spent.
  • 05:00 Rates have a time type greater than 1.
  • 05:03 Fieldnames are surrounded by square brackets.
  • 05:06 The third argument is what to to return
  • 05:08 if the condition is false.
  • 05:11 If time does not apply,
  • 05:13 the expression will be multiplied by 1.
  • 05:16 Everything inside the IIF is enclosed with parentheses.
  • 05:22 Looking at the results, we do not have any records
  • 05:24 with another time type.
  • 05:26 To test the equation, temporarily change
  • 05:28 one of the time type values to 2
  • 05:30 and move off the line to save the record.
  • 05:33 Access recalculates the amount and you can see that
  • 05:36 it was indeed multiplied by 0.5.
  • 05:41 Now delete the value of 2 since that isn't right for this record.
  • 05:46 You can use queries to edit data also.
  • 05:48 Go back to the design view.
  • 05:50 Insert a column before the Customer.
  • 05:53 Select the customer column
  • 05:55 by clicking in the little gray header area,
  • 05:57 and press the Insert key on the keyboard.
  • 06:00 We are going to create an expression
  • 06:02 to tell us what year this work was done.
  • 06:05 Double-click the DtmWork1 field,
  • 06:08 which puts it on the grid and selects it.
  • 06:11 Even though we cannot see it, press Ctrl-X to cut it.
  • 06:15 Now click in the first column
  • 06:17 and type YearWork: to label the column.
  • 06:21 Then type Year([
  • 06:25 then Ctrl-V to paste, then ])
  • 06:30 When you look at the results, you can see the year
  • 06:33 was pulled out of the date for each record.
  • 06:37 To move a column, select it, and let go of the mouse,
  • 06:40 then click in the header area and drag it.
  • 06:44 To delete a column, click in the header area to select it,
  • 06:47 and press the Delete key.
  • 06:49 To make a column wider,
  • 06:51 drag its right border in the header area.
  • 06:54 Label the DtmWork1 column as WorkDate.
  • 06:58 Click on the diskette icon on the QAT to save the query.
  • 07:02 Call it q_CustomerServicesByYear.
  • 07:07 When we build a report, users may want to filter it
  • 07:10 by Customer, Service, or Service Type,
  • 07:13 so we will add the primary keys for each of those tables.
  • 07:17 In the next lesson, we will use this query
  • 07:19 to create a grouped report.

Lesson notes are only available for subscribers.

Gift this course