Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 36 ยท Forms and Reports

Creating Reports in Access: Wizards, Views and Sections

Build Access reports with the Report button and Report Wizard. Learn Report, Layout, Design and Print Preview views, report sections, page setup, labels and PDF export.

A report is how data leaves your database in a polished form: an invoice, a sales summary, a mailing list, a set of address labels, an attendance sheet. Reports read from a table or query, arrange the data in sections and can group, sort and total it. Unlike a form, a report is for output: users read it, print it or save it as a PDF, but do not edit data in it.

This lesson covers how to create reports quickly, the views, the sections every report has and the page setup and export options. The next lesson deals with grouping, sorting and totals, which is where reports really earn their keep.

Creating a report quickly

Select a table or query in the Navigation Pane, then pick from Create:

  • Report: one click. Access builds a tabular report listing every field and opens it in Layout view.
  • Report Wizard: asks which fields, whether to group, how to sort and total, the layout and orientation, then the title. A good choice when you want grouping.
  • Report Design: a blank report in Design view, for full manual control.
  • Blank Report: a blank report in Layout view with a Field List.
  • Labels: a wizard for mailing labels (see below).

The one-click Report is the fastest start. Delete columns you do not want, resize the rest and add a title. The Report Wizard takes slightly longer but sets up grouping and subtotals for you.

From data to paper

Record Source A table or query
Layout Sections, groups, sorting
Preview Print Preview and Report view
Output Print, PDF, Excel, email

Reports never change the data.

1 / 2

Always base reports on queries

A report on a raw table shows the table in whatever order it happens to be stored. Base your report on a query instead, so you can:

  • Join tables (customer name beside each order),
  • Filter (only this year),
  • Calculate (a LineTotal field),
  • Sort reliably, and
  • reuse the same query for forms, exports and other reports.

Name the query clearly, such as qryInvoiceLines. This runnable example prepares the dataset a simple invoice report would read:

SQL The query behind an invoice report

Press Run (or Ctrl + Enter) to see the result here.

The report views

View Use
Report view A screen view with scrolling. You can filter, search and select text, but page breaks are not shown.
Print Preview How pages will look on paper, with margins and page breaks. Zoom, change page size and export from here.
Layout view Edit the design while real data shows. Move columns, resize, change fonts, add grouping and totals.
Design view Full control: sections, controls, properties, VBA events.

A usual working rhythm: build in Layout view, check in Print Preview, and use Design view for details. After you save, double-click the report in the Navigation Pane to open it in Report view.

If a report fills several pages but shows an empty page at the end, the usual cause is a control placed too wide for the page. In Design view, make sure the total width of the controls plus the margins is no wider than the page.

Report sections

In Design view, a report has up to seven kinds of sections. Their order matters:

Section When it prints
Report Header Once, at the very start. Put a logo and report title here.
Page Header At the top of every page. Column headings go here.
Group Header At the start of each group (one per grouping level).
Detail Once for each record. Your field controls go here.
Group Footer At the end of each group. Subtotals go here.
Page Footer At the bottom of every page. Page numbers and dates.
Report Footer Once, at the very end. Grand totals go here.

Add or remove header and footer pairs by right-clicking the report background: Report Header/Footer and Page Header/Footer. Group sections appear when you add a group.

Think of the sections as a layered sandwich: the report header and footer wrap everything, page headers and footers repeat on every sheet and group and detail sections repeat as many times as the data needs.

Page numbers, dates and titles

Add them from the Header/Footer group of the Report Design or Design tab:

  • Logo and Title go in the Report Header.
  • Date and Time inserts text boxes with =Date() and =Time().
  • Page Numbers inserts an expression like ="Page " & [Page] & " of " & [Pages] in the header or footer.

[Page] and [Pages] are built-in report properties. You can write your own, for example =[Page] & " / " & [Pages] or ="Printed " & Format(Now(),"dd mmm yyyy hh:nn").

Page Setup

Use Page Setup on the Print Preview tab (or Report Design > Page Setup) to set:

  • Paper size and Orientation (Portrait or Landscape).
  • Margins.
  • Columns: multiple columns across the page, as in a phone list.
  • Show Data Only (a checkbox for printing without labels and lines).

A wide table usually needs Landscape. If a column sticks out past the right margin and prints a stray page, shrink the controls, reduce the margins or switch to Landscape.

You can also set the width of the report itself in the Property Sheet, under Format > Width.

Creating labels

The Labels wizard produces mailing labels. Select the table or query that holds the addresses, click Create > Labels and follow the steps:

  1. Pick the manufacturer and product number of your label stock (Avery and others). Choose Custom if yours is not listed.
  2. Choose font and color.
  3. Build the label text: select a field, click > and add spaces or line breaks.
  4. Choose a sort field and name the report.

A label is simply a report with several columns and a tiny Detail section. Always print a test page on plain paper first and hold it against the label sheet to the light.

Printing, PDF and export

From Print Preview, the Data and Print groups let you send the report to many places:

  • Print (Ctrl+P) to a printer.
  • PDF or XPS to save a fixed-layout file you can email.
  • Excel, Text File, Word (RTF), XML or HTML for the data, with layout lost.
  • Email as an attachment.

You can also run File > Save As > Save Object As > PDF or XPS or, from VBA, DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, "C:\Invoices\inv100.pdf".

Filtering a report when opening it

You do not want to edit a report for each customer. Instead, filter it when you open it. Right-click the report in the Navigation Pane and choose Open, then in Report view apply a Filter from the Home tab. Or, from a form button, open it with a WHERE condition. In a macro, the OpenReport action has a Where Condition argument, such as [CustomerID]=[Forms]![frmCustomers]![CustomerID]. In VBA:

VBA
DoCmd.OpenReport "rptInvoice", acViewPreview, , "OrderID = " & Me.OrderID

The Where Condition is exactly what you would put after WHERE in the report's query, for a single order. In SQL terms, this is what the report receives:

SQL One invoice: the Where condition applied

Press Run (or Ctrl + Enter) to see the result here.

Reports with no data

If the query returns no rows, a report prints "#Error" in calculated controls or shows blank headers. Use the report's On No Data event to display a message ("No records found") and cancel opening. The macros chapter shows how.

Design checklist

  • Title that says what and for what period.
  • Repeated column headings in the Page Header.
  • Consistent alignment: numbers right-aligned, text left-aligned.
  • Totals clearly separated from detail rows.
  • Page numbers and the printed date.
  • Fonts no smaller than 9 pt.
  • Preview at 100% before printing.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Where do column headings that should repeat on every page belong?

    The Page Header prints at the top of every page. The Report Header prints once.
  2. 2. Which section holds a grand total that prints once at the end?

    The Report Footer prints once, after all the data.
  3. 3. Why base a report on a query rather than a table?

    A query shapes the data before the report sees it, and can be reused.
  4. 4. What does the expression ='Page ' & [Page] & ' of ' & [Pages] show?

    [Page] and [Pages] are built-in properties of a report.

Summary

  • Create reports with Create > Report or the Report Wizard; base them on queries that join, filter and calculate.
  • Use Report view, Layout view, Print Preview and Design view for different jobs.
  • A report has Report, Page, Group and Detail sections; headers and footers repeat as their names suggest.
  • Page Setup, labels and PDF export finish the job; filter reports with a Where Condition when you open them.
  • Keep layouts tidy: repeated headings, aligned numbers and page numbers.

Next: Grouping, Sorting and Totals in Reports