Create a Query, Grouped Report and Auto Report

Locked lesson.

Upgrade

About this lesson

Create a query from scratch to line up data for a report that is grouped by something.

Exercise files

Download this lesson’s related exercise files.

Services_06_start.accdb
596 KB Services_06_end.accdb
844 KB

Quick reference

Create Query and Reports

Application Terminology

[Page]

[Page] is a variable that displays the current page number and can be used in an expression in a Control Source.

[Pages]

[Pages] is a variable that displays the total number of pages and can be used in an expression in a Control Source.

Ascending

When sorting fields, ascending order is from 0 to 9 then A to Z.

AutoReport

Click on the Report icon in the Reports group of the Create ribbon to create a basic report showing fields in the selected source (table or query).  If a form or report is selected when this button is clicked, a report will be made from the Record Source.  Fields that are hidden will not be displayed.

Available Fields

Available Fields is the list of fields you can choose to show on a Report in the Report Wizard.

Best-fit

To best-fit a control, double-click one of its regular handles.  Labels are best-fit for width and height.  Controls containing data are best-fit for height only.  The size is dependent on properties such as Font, Font Size, Margins (Top, Bottom, Left, Right), and Padding (Top, Bottom, Left, Right).

Block

A Block layout for a report lines up the Left properties of each level.  Detail fields line up under grouped fields.  Labels are in the Page Header section over all the fields.

Caption

The Caption property for a control defines what will be displayed in that control when viewed.  The Caption property of a form or report is what will be displayed in the title bar when the object is opened or previewed.

Control

A control is a defined area on a form or report. Controls can contain fields.  Controls can also be labels, lines, rectangles, and more.  Everything displayed on a report or form is in a control.

Control Source

Use the Control Source property to specify what is displayed in a control.  It can be a field in the underlying Record Source, or an expression.  If an expression is used, it must begin with an equal sign (=).

Descending

When sorting fields, descending order is from Z to A then 9 to 0.

Design View

The Design View of Report looks very much like the design view of a Form.  Controls display in a different place depending on whether they are in the Report Header, Page Header, Detail, Report Footer, Page Footer, or Grouped sections.  

The Design View of a report enables you to make changes to the report definition.

Detail Section

Controls in the Detail Section print for every record.

Group

In a Report, fields can have up to 10 levels of sorting and grouping.  A grouped field can have subtotals in the group header and the group footer. All records in the grouped section have the same value in the field that is used for grouping.

Group Footer

Controls in a Group Footer are displayed at the bottom of a grouped field, just before the next Grouped Header.

Group Header

Controls in a Group Header are displayed each time the grouped field for that section changes.

Grouped Report

A Grouped reports shows data divided into sections by field values.  For instance, you may want all customer information to appear together.  This difference between sorting and grouping is that groups can have sections for group header and group footer.

Landscape

Landscape orientation means that a page is wider than it is tall.

Layout View

Layout View displays a subset of data on a report and allows you to make limited design changes

Now()

Now() is a functions that displays the current date and time.

Orientation

Orientation is the way a page is positioned when viewing.  Choices are Portrait or Landscape.

Outline

Outline layout for a report indents each level including grouped fields and labels the detail records on a group level instead of over the report.

Page Footer Section

Controls in the Page Footer Section print once at the bottom of each page.

Page Header Section

Controls in the Page Header Section print once at the top of each page.

Portrait

Portrait orientation means that a page is taller than it is wide.  This is the default orientation.

Print Dialog Box

The Print Dialog Box lets you choose a printer (and specify its properties), which pages to print, and the number of copies.

Print Preview

Previewing a report means you will look at it on the screen as opposed to sending it to a printer.

Query

Queries are often the first step to make a report as they can pull information from more than one table. Queries can also sort and filter information.

Quick Access Toolbar (QAT)

The Quick Access Toolbar, or QAT for short, is a row of icons above the ribbon that has common functions like Save and Undo.  You can customize the QAT and choose to show it below the ribbon instead of above.

Record Source

The Record Source of a form or report is the name of a table, query, or SQL statement.  The Record Source specifies the fields that will be available.

Relationships Diagram

The Relationships Diagram is a good way to get an overall view of what a database is tracking.  The Relationships icon is on the Database Tools ribbon.

Report Footer Section

Controls in the Report Footer Section print once at the bottom of the report.

Report Header Section

Controls in the Report Header Section print once at the top of the report.

Report View

Report View displays data in one continuous report with no page breaks, like a web page.

Report Wizard

The Report Wizard is a set of dialog boxes that help you build

Sections

Sections in a report include Report Header, Page Header, Detail, Report Footer, Page Footer, or Grouped sections.   To turn sections on or off, right-click in any section and toggle choices on the shortcut menu.  To change the height of sections, click on the bottom border of a section and drag it.

Selected Fields

Selected Fields is the list of fields chosen in the Report Wizard for a Report.

Sort

On a report, there can be 10 levels of sorting and grouping. Fields can be sorted in Ascending or Descending order.

Stepped

A Stepped layout for a report indents each level including grouped fields.  Labels are in the Page Header section over all the fields, including grouped fields.

View

A Report can be viewed in Report View (like a web page – this is one continuous report with no page breaks), Print Preview (view on screen with page breaks), Layout View (make limited design changes while viewing a sample of the data), and Design View (make changes to the report definition).

Steps

Show Relationships Diagram

  • To show the Relationships Diagram, click the Relationships icon on the DATABASE TOOLS ribbon tab

Add Tables to Relationships Diagram

  • To add tables to Relationships Diagram, drag them from the Navigation Pane
  • or click the Show Table command on the DESIGN ribbon tab of RELATIONSHIP TOOLS

Create a Query from scratch

  1. Click on the Create ribbon and choose Query Design.
  2. Double-click table names or query names from the Show Table dialog box
  3. or close the Show Table dialog box and drag the tables from the Navigation Pane

Add Fields to a Query

  1. To Add Fields to a Query, double-click them from a fieldlist
  2. or Drag them from a fieldlist to the column where you want them to go
  3. or Click on the drop-down arrow in the Field cell and choose fields from the list
  4. or Type what you want in the Field cell.

Best-Fit or Resize Column in Query Design

  1. Move the mouse to the right border in the short gray header area
  2. To Best-Fit, double-click
  3. To Resize, click and drag

Create a Report

  1. Select the table or query you want to base the report on in the Navigation Pane, if desired, to save time
  2. Click the type of report you want from the Reports group in the Create ribbon

Best-Fit a Control

  1. To best-fit a control for its contents, select it
  2. and double-click on a resizing handle
Login to download
  • 00:04 This is lesson 6 of Access 2013.
  • 00:08 In this lesson, I will show you how to
  • 00:10 make a query to line data up for a report
  • 00:12 that is grouped by something.
  • 00:14 I will also show you how to make a quickie AutoReport.
  • 00:18 To report data, you need to understand what data you have.
  • 00:22 A good way to get an overall view of what is in a database
  • 00:26 is to look at the Relationships Diagram.
  • 00:28 Hi this is Crystal.
  • 00:30 On the Database Tools ribbon, click the Relationships icon.
  • 00:33 This shows that the Services table has 2 lookup fields --
  • 00:38 one to Service Types, which define things
  • 00:41 like Lawn Care, Plumbing, and Electrical,
  • 00:43 and one to Time Types, which specify
  • 00:46 if the price is fixed or will be billed by the hour.
  • 00:50 There is also a table for Customers
  • 00:52 but it is currently not linked to any other table.
  • 00:55 When you open the services table,
  • 00:57 it is hard to tell what the service type
  • 01:00 and time types are because the services table stores numbers.
  • 01:03 The Services table is Joe's list so down the road
  • 01:07 when he enters job information,
  • 01:09 what he did can be looked up.
  • 01:11 Joe could also print a report of services
  • 01:13 to help market his company.
  • 01:16 The first step in creating a report
  • 01:18 involving more than one table is to make a query.
  • 01:21 Queries can get information from more than one place.
  • 01:26 Click on the Create ribbon and choose Query Design.
  • 01:29 Close the Show Table dialog box that appears
  • 01:31 because it is quicker to drag the tables you want
  • 01:34 from the Navigation Pane.
  • 01:36 When you have more than one table in the source of a query,
  • 01:39 Access shows you defined and inferred relationships
  • 01:43 using a line that may or may not be labeled at the ends.
  • 01:47 This line tells us that for every one record in service types,
  • 01:51 there can be many records in services.
  • 01:54 Double-click ServType from the service types table
  • 01:57 to put that field on the grid.
  • 01:59 Then double-click ServName from the Services table.
  • 02:02 Sort in Ascending order first by service type
  • 02:06 and then by service.
  • 02:08 Click on the diskette icon on your Quick Access Toolbar
  • 02:11 to Save the query.
  • 02:13 Name the new query qServices_by_Type.
  • 02:19 Click the Datasheet View icon on the Home ribbon
  • 02:22 to see the data.
  • 02:23 For each service type, there are several services.
  • 02:27 Service type would be a good field to group by.
  • 02:30 Close the query and select it in the Navigation Pane.
  • 02:34 On the Create ribbon,
  • 02:35 click the Report Wizard icon in the Reports group.
  • 02:39 Because the query was selected first,
  • 02:42 it is already filled in as the source for the report
  • 02:45 and the Available Fields show on the left.
  • 02:48 Click on the double angle bracket
  • 02:50 to move all fields to the Selected Fields box.
  • 02:53 Click Next.
  • 02:55 Because there are 2 tables involved,
  • 02:58 Access makes a guess at how the data should be grouped.
  • 03:01 This is indeed what we want --
  • 03:03 a list with everything by service type.
  • 03:05 Click Next.
  • 03:07 Within each grouping, how should the data be sorted?
  • 03:10 Choose the service name,
  • 03:12 which is the only other field on this report.
  • 03:14 Click Next.
  • 03:16 Now you can pick a style for the report.
  • 03:18 The default, Stepped, is fine as
  • 03:20 we will change what the wizard does anyway.
  • 03:23 Click Next.
  • 03:24 Name the Report r_Services_by_Type and click Finish.
  • 03:34 With a few changes, we can make this report look really good.
  • 03:37 To see as much as we can,
  • 03:39 double-click the titlebar of the report
  • 03:40 to put it into maximized view.
  • 03:43 Click Close Print Preview from the ribbon.
  • 03:46 The Design View of a report looks very much
  • 03:49 like the design view of a form,
  • 03:51 so everything you have been learning about forms
  • 03:54 will make it easier for you to modify reports too.
  • 03:57 Like a form, a report also has sections.
  • 04:01 The Report header section prints once
  • 04:03 at the beginning of the report.
  • 04:05 I usually use this section if I want a Title Page.
  • 04:09 The Page Header section shows at the top of every page.
  • 04:13 ServType Header is a group header and
  • 04:15 shows each time the service type changes.
  • 04:19 The Detail section shows for every record.
  • 04:22 The Page Footer section shows
  • 04:24 at the bottom of each page.
  • 04:27 The Report Footer shows once
  • 04:29 at the end of the report.
  • 04:31 Delete the labels from the Page Header section
  • 04:33 and move the report title
  • 04:35 from the Report Header section
  • 04:36 to the Page Header section by dragging it.
  • 04:39 Change the Caption of the title to
  • 04:41 Joe Smith Handyman Services,
  • 04:44 and the font to something smaller.
  • 04:46 double-click on a handle of the label
  • 04:48 to best-fit it to the smaller size.
  • 04:51 Drag the bottom border of the page header section
  • 04:54 up to close the space.
  • 04:56 Also drag the bottom border of the Report Header section up.
  • 05:01 Move the control with the service type to the left,
  • 05:03 make the font bigger and bold,
  • 05:05 and double-click on a handle to best-fit the size.
  • 05:09 In the Detail section,
  • 05:10 move the Service name to the left but leave a little space
  • 05:13 so it is clear that it is under service type.
  • 05:17 Click on the diskette icon on the Quick Access toolbar
  • 05:20 to save the changes.
  • 05:22 Drop the (view) choices and choose Print Preview.
  • 05:25 This report is really starting to look good.
  • 05:28 To see more of the report at once,
  • 05:30 collapse the ribbon by double-clicking
  • 05:32 on one of the ribbon tabs.
  • 05:35 Click on the report itself to zoom in or zoom out.
  • 05:39 Joe now has a nice report that he can print out
  • 05:42 and give to customers.
  • 05:44 One way to Print is to right-click on the report
  • 05:46 and choose Print from the shortcut menu.
  • 05:49 The Print dialog box comes up and
  • 05:51 you can choose the Printer,
  • 05:53 how many copies,
  • 05:54 and other things like which pages.
  • 05:56 Click on the Print Preview ribbon tab
  • 05:59 and click Close Print Preview.
  • 06:01 Then close the report design view
  • 06:04 by clicking the X in the upper right corner.
  • 06:07 For a quickie report,
  • 06:08 select the Customers table in the Navigation Pane.
  • 06:12 Then click the basic Report icon
  • 06:15 in the Reports group of the Create ribbon.
  • 06:18 Very quickly, Access makes a report with a list of customers.
  • 06:22 Just like we modified the report
  • 06:24 that was created using the wizard,
  • 06:26 we could go into the design
  • 06:28 or layout view of this report and make changes.
  • 06:31 Click the X in the upper right corner to close the report.
  • 06:34 When prompted to save,
  • 06:36 name the report: r_Customers
  • 06:39 Now there are 2 reports listed in the Navigation Pane.
  • 06:43 Open a report by double-clicking on it.
  • 06:46 With reports, you can organize your information
  • 06:49 and make it look really nice.
  • 06:51 The next couple lessons will be about importing data
  • 06:54 from other Access databases and from Excel workbooks.

Lesson notes are only available for subscribers.