Subscriber only lesson.
Sign up to this course to view this lesson.
Create Query and Reports
[Page] is a variable that displays the current page number and can be used in an expression in a Control Source.
[Pages] is a variable that displays the total number of pages and can be used in an expression in a Control Source.
When sorting fields, ascending order is from 0 to 9 then A to Z.
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 is the list of fields you can choose to show on a Report in the Report Wizard.
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).
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.
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.
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.
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 (=).
When sorting fields, descending order is from Z to A then 9 to 0.
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.
Controls in the Detail Section print for every record.
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.
Controls in a Group Footer are displayed at the bottom of a grouped field, just before the next Grouped Header.
Controls in a Group Header are displayed each time the grouped field for that section changes.
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 orientation means that a page is wider than it is tall.
Layout View displays a subset of data on a report and allows you to make limited design changes
Now() is a functions that displays the current date and time.
Orientation is the way a page is positioned when viewing. Choices are Portrait or Landscape.
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 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.
Previewing a report means you will look at it on the screen as opposed to sending it to a printer.
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.
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.
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 displays data in one continuous report with no page breaks, like a web page.
The Report Wizard is a set of dialog boxes that help you build
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 is the list of fields chosen in the Report Wizard for a Report.
On a report, there can be 10 levels of sorting and grouping. Fields can be sorted in Ascending or Descending order.
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.
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).
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
Lesson notes are only available for subscribers.