Locked lesson.
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.accdb596 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
- Click on the Create ribbon and choose Query Design.
- Double-click table names or query names from the Show Table dialog box
- or close the Show Table dialog box and drag the tables from the Navigation Pane
Add Fields to a Query
- To Add Fields to a Query, double-click them from a fieldlist
- or Drag them from a fieldlist to the column where you want them to go
- or Click on the drop-down arrow in the Field cell and choose fields from the list
- or Type what you want in the Field cell.
Best-Fit or Resize Column in Query Design
- Move the mouse to the right border in the short gray header area
- To Best-Fit, double-click
- To Resize, click and drag
Create a Report
- Select the table or query you want to base the report on in the Navigation Pane, if desired, to save time
- Click the type of report you want from the Reports group in the Create ribbon
Best-Fit a Control
- To best-fit a control for its contents, select it
- and double-click on a resizing handle
- 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.