Skip to Main Content
University of York Library
Library Subject Guides

Essential Access: a Practical Guide

Forms and reports

In this section, we'll explore the world of creating reports and forms in Access, and how to configure your reports to appear as you want and modify forms to have appropriate controls, subforms, lookup fields, and even buttons.

Creating reports

A report is a summary of data retrieved from the database, usually presented in a more user-friendly format than the simple datasheet generated by a query. Reports can group information, perform additional calculations, and generate nicely-formatted printed copies. However, reports cannot be used for data entry or editing (that requires a form).

Reports are always based on a table or, more usually, a query, which means they are always compiled using current data and if the underlying query changes, so does the report. Some examples are shown below:

You can have grouped or tabular reports in Access - tabular is like a regular table and grouped collects together records using a common field value.

Creating a report with the Report Wizard

The Report Wizard enables a report to be constructed from choices, meaning you can ensure only relevant data are presented in the most useful format.

To construct a report you will need to:

  1. Decide which fields of data are needed and identify the relevant tables.
  2. Create and save a query based on these tables, including the appropriate table fields, plus any calculated or concatenated fields if these are needed.
  3. Launch the Report Wizard by selecting Create > Reports > Report Wizard.

Once the Report Wizard is open, there are a number of steps you will go through to create your report. These steps may not exactly match the sequence below as what you see does depend on your data, but this gives an example:

  1. Select the query or table on which the report is to be based and add the required fields from the list of Available Fields to the Selected Fields list. If you're going to group data, make sure you include any relevant key fields.
  2. You may be asked how you want to view your data. Although you can group records at this point, Access may not select the correct field, so it's often better to choose the option that does not group records at this point.
  3. If appropriate for your data, you will be asked to define grouping levels. This steps allows you to select the correct field for grouping, usually a primary key.
  4. At this point you can define a sort order for records. This does not affect grouping, only the records within a group.
  5. Select the style of layout required. The options here will depend on whether or not you have defined grouping or not - only Tabular (table-style) layout is availabe is no grouping is used.
  6. Provide a report title. This will also be used as the object name, but both c an be changed later to match any naming convention you are using. Select Finish to preview the report (do this before choosing to modify the design).

The report will be generated based on yuor choices, but it is unlikely to look exactly how you want and will require further editing.

Report views

There are four views (like design and datasheet view for tables and queries) available for reports:

ReportThe compiled and formatted report intended for onscreen viewing, but not for printing.
Print PreviewA preview showing how the report would appear when printed using the current settings for paper and margins. You can adjust page and paper settings and print from this view.
LayoutThe compiled and formatted report with the ability to adjust the width, height, and position of the data fields.
DesignFull control of the structure, content, and functionality of the report is only available from the more complex Design review.

When creating and editing reports you will mostly use the Design and Layout views, choosing Report and Print Preview to check the onscreen and printed views.

Editing reports

In order to edit a report you need to use the Design view, which is divided into sections with each section corresponding to a portion of the compiled report.

An grouped report has a more complex structure in the Design view than an ungrouped report. We will cover each separately first before looking at some other features of report editing.

Using Report Design view with no grouping

An ungrouped report created with the wizard will have 5 sections, though the Report Footer. may be set to zero height. The sections are:

Report HeaderContent is shown only once at the top.
Page HeaderContent is shown at the top of every page.
DetailThis section displays the data and is repeated for each record found in the underlying query or table.
Page FooterContent is shown once at the foot of every page.
Report FooterContent is shown once only at the end of the report, at the end of the data (before the Page Footer).

The sections in the Report design view match up with sections of final report.

Using Report Design view with grouped reports

A grouped report has a more complex structure including a Group Header above the Detail section. In some circumstances you may also need a Group Footer below the Detail (e.g. for subtotals).

When a grouped report is compiled the Group Header - Detail - Group Footer sequence is repeated for each different value found in the field chosen for grouping.

Report HeaderContent is shown only once at the top.
Page HeaderContent is shown at the top of every page.
[Group]* HeaderFor each group of records, displays data relevant to that group
DetailThis section displays the data and is repeated for each record found in the underlying query or table.
[Group]* FooterFor each group of records, displays data relevant to that group.
Page FooterContent is shown once at the foot of every page.
Report FooterContent is shown once only at the end of the report, at the end of the data (before the Page Footer).

*The group header and footer display the name of the field chosen for grouping.

Group header, detail and footer are repeated for each group field values.

Field properties

The Hide Duplicates property is particularly useful as it will prevent the display of repeated values in the detail section. To modify a field property:

  1. In Design view, select the text box for the relevant field and view the properties list - either select Report Design Tools > Design > Tools > Property Sheet or right click and choose Properties.
  2. On the property sheet Format tab locate the Hide Duplicates property and set it to Yes using the drop-down control.

If Hide Duplicates is set to No, there will be repeated values in each group, whereas if it is set to Yes, there won't be duplicate values.

Report controls

Every object on a report is referred to as a control. Most data from the underlying query will be presented using a pair of controls:

  • A Text Box containing the name of the underlying field, which will display the retrived data - you must not edit this name.
  • A Label containing the name of the field or the caption if one is defined - you can edit the content of the label without affecting data display.

Tip

The names of Text Boxes and Labels can be confusing, as a Label contains text you can edit and a Text Box links to the data in the underlying query or table. It can also be difficult to spot which is which in Design view so you might need to change to Layout view to check which one contains the data.

Manipulating controls

All controls have a collection of properties that define size, appearance, and behaviour. Many of these can be configured from the ribbon (Home > Text > Formatting) and the size adjusted using the sizing handles.

If the text box and label for a field are in the same report section they can be moved together.

Controls have move and resize options. You can modify fonts from the Home tab. To move both the Text Box and Label controls together drag on the border of the control. The top left handle moves controls individually

Other controls

If a Yes/No field is included in the underlying data, the Wizard will render this as a checkbox. When the intention is to print the report, this is the only other control type that is likely to be used.

A report may also included graphic elements. For example, lines may be added to enhance readibility or other images may be included. These are inserted in the appropriate section via Report Design Tools > Design > Controls.

Default footer controls

The Report Wizard automatically places two Text Box controls on the Page Footer section containing functions to display system information on printing.

=Now() displays the current date and time.

="Page " & [Page] & " of " & [Pages] Displays the current page number [Page] and total number of page numbers [Pages], using concatenation, so it shows in the format Page 2 of 6

Group & Total configuration

Grouping and sorting have their own set of controls that can be used even if you did not initially choose grouping in the wizard. This allows you to:

  • add multi-level grouping
  • refine sort orders
  • choose which sections to include
  • add totals to header or footer sections
  • control how a group splits on a page

To use these additional controls:

  1. In Design view select Report Design Tools > Grouping & Totals > Group & Sort and an extra pane will appear at the bottom of the screen.
  2. If groupng is already set, choose More to expand the controls. If no grouping is set, choose Add a group first.
  3. Configure the grouping as appropriate.
  4. Options in the expanded controls allow you to change the visibility of the group header and footer.

Using the More option in Group, Sort and Total will offer more controls about having group headers and footers visible.

Section properties

The alyout of a completed report can be further ocntrolled by modifying the properties for each section. To view and modify properties for a section:

  1. View the property sheet using Report Design Tools > Design > Tools > Property Sheet.
  2. Choose the section - either click on the labelled bar on an an empty area of the section.

Of particular interest are the properties that allow you to allow a region to grow/shrink with the amount of content, ensure all content in a section is kept together on the same page, and force the start of a new page before/after the section.

Page size

When designing for printed output, bear in mind you will be limited to paper width. Use the controls of the Report Design Tools Page Setup tab and rule to adjust as needed.

The report width (edited using report properties or rule) plus page margins must not exceed 21cm (A4 portrait) or 29.7cm (A4 landscape). Access will warn if the report width is too great for the defined paper and margin sizes.

Calculated fields

Totals and subtotals can be added to a grouped report using the Group and Total configuration controls (see above), but may also be added using an unbound Text Box control - meaning one that is not tied to a specific field in the underlying data. An expression is then entered that references field names using appropriate syntax.

To insert an unbound text box:

  1. View the report in design view and from Report Design Tools > Design > Controls select the Text Box control.
  2. Position the cursor over the relevant section and click to place the unbound Text Box. it will be inserted with an accompanying Label.

An unbound text box will be inserted with the text Unbound in it, and an accompanying label to be edited with suitable text
Tip

Remember that a Text Box contains data (in this case, an expression to be calculated) and a Label contains text as a label for that data.

Placing the control in the Group Header or Footer will perform the calculation of the records in each group.

Placing the control in the Report Footer will perform the calculation for all records in the report.

Number functions and arithmetic

Expressions can be constructed using functions, field names, text, and numbers.

Tip

Excel users should note that Access and Excel functions are similar but may have different names.

Some tips for creating expressions:

  • You must always start with an equals sign =
  • Function names are followed by a pair of brackets enclosing any parameters (information for the function to use to run)
  • Field names must be enclosed in square brackets
  • The usual mathematical operators can be used +-*/
  • Additional text, including spaces, must be enclosed in double quotes
  • Multiple elements must be joined using an ampersand &
FunctionExample
Sum (total)=Sum([FieldName])
Average=Avg([FieldName])
Count=Count([FieldName])
Max=Max([FieldName])
Min=Min([FieldName])
Arithmetic= [Cost] * [Quantity] * 10%
Combined calculations=Avg([Cost] * [Quantity])
Including text="Average is: " & Avg([FieldName])

To control the display of numerical data you must modify the Text Box properties:

  1. In Design view, select the text box for the relevant field and view the properties list - either select Report Design Tools > Design > Tools > Property Sheet or right click and choose Properties.
  2. On the property sheet Format tab set the Property Format from the drop-down list. If you choose Fixed also set the property for the number of decimal places.

SEt decimal places by choosing Fixed format and setting decimal places

Text manipulation

Text data can be concatenated (using an ampersand &) in an expression and can be manipulated with text functions. Additional text, including spaces, must be enclosed in double quotes.

Text formatExample
Simple concatenation with space between=[Forename] & " " & [Surname]
Fields combined with text="First name is: " & " " & [Forename]
Initial letter from Forename field=Left([Forename],1)
Surname, Initial=[Surname] & ", " & Left([Forename],1)

Other expressions

ExpressionWhat it does
=Now()Current date and time
=Date()Current date
=If([Price]>25, "too big", [Price]])Immediate IF - works like Excel's IF
=[Page]Current page number
=[Pages]Number of pages in report
=[Name]Name of report object (not report title)

Creating forms

In Access, forms are used to provide more user-friendly on-screen interaction with data than is possible with tables and queries. Forms can include familiar controls such as drop-down lists and buttons. They interact with the underlying tables, but do not themselves store data. Ideally they need:

  • a task focus: they should be desgined around a specific task to be undertaken
  • a user focus: no knowledge of the underlying data structure should be needed in order to carry out the required task.

Forms can be designed to display one record at a time, a continuous list of records, or one main record (on the main form) along with continuous records of related data (on the subform).

You can have forms displaying one record, a continuous list of records, or a main record alongside a subform of continous related records

Creating a form with the Form Wizard

When you choose a query to create a form, the Form Wizard analyses your data relationships and offers various ways of displaying the data. Some choices will generate a main form/subform combination, some a single form. If the end result does not meet your needs, delete and re-run the wizard.

When constructing a form:

  • Decide what the user will need to do with the form.
  • Determine which fields of data will need to be edited or entered.
  • Decide if the user also needs to be able to view other fields too.
  • Unless this data is present in only one table, construct a suitable query.
  • Launch the Form Wizard by selecting Create > Forms > Form Wizard.

The Wizard process will depend in part on your data. Read the questions posed at each step in the wizard and respond as you think appropriate to define the form.

Choose the data source and fields, then complete the remaining steps.

Form views

As with other objects, there are several views, with some dependent on your data structure::

FormThe on-screen form with which users will enter, view, and edit data.
DatasheetData presented in the format of a datasheet. It looks the same as a table/query datasheet.
LayoutThe on-screen form with the ability to adjust the width, height, and position of the data fields - use for editing layout.
DesignFull control of the structure, content, and functionality of the form is only available from the more complex Design review.

Forms based on a simple data structure may also offer you:

  • Pivot Table View - creates a pivot table based on your data.
  • Pivot Chart View - creates a chart based on your data.

Editing forms

Detailed editing of forms is carried out in Design view, with layout possible to be adjusted in Layout view. Editing form design is very similar to editing report design.

From design view has three sections: Header, Detail, and Footer. The content of the Header and Footer generally remain fixed, and record data displays in the Detail section.

Every item on a form is referred to as a control. Data from an underlying table or query is usually presented as a pair of controls:

  • A Text Box containing the name of the underlying field, which will display the retrived data - you must not edit this name.
  • A Label containing the name of the field or the caption if one is defined - you can edit the content of the label without affecting data display.
Tip

The names of Text Boxes and Labels can be confusing, as a Label contains text you can edit and a Text Box links to the data in the underlying query or table. It can also be difficult to spot which is which in Design view so you might need to change to Layout view to check which one contains the data.

Single and continuous records

There are two options for forms in Design view: single and continuous record view. Single record view always presents one record at a time, whereas when choosing continuous records the detail section is repeated for each available record in the underlying data, filling the available space between the form header and footer.

Setting a single record view in form design will only show the exact fields chosen once on the form for a single record, whereas setting continuous record view will show all the fields chosen for multiple records.

Form controls and field properties

Adding form controls

In form design view you can edit a range of elements, as with reports. Form controls can be edited using the same methods as report controls and any other controls needed can be added to the form. For example, you might add more fields to the form using features like a combo box (see Lookups below). To add more controls:

  1. View the field list: Form Design Tools > Design > Tools > Add Existing field.
  2. Select the type of control to use (text box, check book, combo box, etc).
  3. Drag the field from the field list onto the form.
Tip

To add text to the form, choose the Label control and click where you want the text to be positioned - don't use a Text Box control as this expects data.

Some of the form design controls include text box, label, combo box, check box and radio button

Form and field properties

Several form features are controlled using the form properties. To view the form properties, in Design view ensure that no controls or sections of the form are selected and then view the properties list by going to Form Design Tools > Design > Tools > Property Sheet.

Some useful form properties are:

PropertyTabUse
Default viewFormatSwitch between single record and continuous records. There are other options here too.
Record selectorsFormatTurn off/on the grey box to the left of the detail section that allows you to select a record.
Record sourceDataIndictates that table/query on which the form is based.
Pop upOtherForm will always remain on top, even when not active.

There are also some useful field properties. When you have the property sheet open, select a section or field to see its properties. Some useful ones are:

PropertyTabUse
FormatFormatUse in conjunction with Decimal Places property to control display for numerical data.
Show date pickerFormatWhen set to For Dates (default) date fields display a small calendar to choose the date.

Subforms

One feature of a relational database is that a single record in one table is frequently related to several records in a related table or tables. A subform allows you to view both at the same time and is configured so the subform keeps in-step with the main form (so it changes as you cycle through records).

The main form is set to single record view and the subform is set to continuous view

In order for the main form and subform to synchronise, both must share a common field, usually the primary key of the data on which the main form is based.

There are two ways that a form-subform combination can be constructed, either by using the Wizard in one step or by creating the main form and subforms separately and then combining. Whichever approach you use, two new forms will be present in the object list.

Creating subforms with the wizard

To create both the main form and subform at the same time using the wizard:

  1. First create and save a query that contains all the fields you will need on both the main form and subform.
  2. Using the Form Wizard, build a form based on this query.
  3. At the step that asks "How do you want to view your data?" choose the option that places the appropriate fields in the main form (top) and subform (bottom) regions. Also check the option Form with subform(s) is selected.
  4. Continue with the remaining wizard steps to create your form.

Wizard-free subforms

To make a form and subform without using the wizard, first decide which fields will be required on the main form and which on the subform, making sure a common field is available to synchronise records - this will generally be the primary key for the main form data.

Create any queries on whic hthe forms will be based and then contruct the main form and subform. Note that the main form must be in single record view and the subform must display either continuous records or datasheet view.

To combine the main form and the subform, make sure the main form design allows space for the subform, and then:

  1. Open the main form in design view. Locate the subform in the Object list and drag it onto the main form.
  2. In desig nview, select the subform and view the properties. On the Data tab configure the synchronisation with Link Master Fields being the key field on the main form and Link Child Fields being the corresponding field on the subform. Selecting the ellipsis (...) will open a dialogue to configure these).

You can also add the subform to the main form using the Subform/Subreport item in the Controls gallery.

Once this is done, you can tweak the layout using the design view of the main form. Any other subform settings must be configured by opening the subform directly in design view. One change you may want to make is to remove the subform navigation controls: you can edit these and scroll bars from the subform format properties.

Subform calculations

As the subform is synchronised with the main form it is in effect filtered so it is possible to perform additional summary calculations just on these records. These are usually placed in the footer region of the subform using an unbound text box. Expressions are constructed using functions, field names, text, and numbers. Unbound controls are inserted from the controls gallery using the same method as for reports.

Lookup fields

Simple display or entry into underlying tables may not always be the most useful approach from a user perspective. Access provides a lookup features that improves usability by allowing the user to choose a value from a list that corresponds to a different underlying value.

Tip

The Lookup feature also exists for table fields, but when used in a table it is inherited by subsequent queries, reports, and forms. This can cause confusion with queries, as the value displayed isn't the value stored in the field. However, lookups are useful when added just to forms.

Consider a form that requires the entry of a department code. Many users would prefer to choose from a list of department names rather than remember the codes. Using a Lookup, the field would obtain its value by "looking it up" in a list, and this would be presented to the user. The list can be:

  • Hard-coded (manually written) into the field properties - you enter a list of options into the control properties (simplest but least flexible)
  • The field names from a table or query (not common, so not covered here)
  • Values already stored in another table or query (most flexible)
A Lookup presents limited options, reducing input errors by users.

If you are replacing an existing Label/Text book control with a lookup, you must first delete these from the form.

Adding a lookup field

You need to be able to see the list of existing fields and the gallery of controls (both on the Form Deisgn Tools > Design tab). Also check the control wizard is enable to use the wizard, or disabled to use manual configuration (found in the More option for the controls gallery.

  1. Select the Combo Box control and then drag the field from the list onto the form.
  2. Complete your selections via the wizard or for manual configuration switch to the property sheet.

Configuring a lookup

The property settings depend on wehterh you want to hard-code the list of look up a value in a table/query.

td>These properties determine whether the user can use values not in the list, and whether new values can also be added for future use.
Property (Tab)Hard-coded value listLookup in another table/query
Row Source Type (Data)Must be Value ListMust be Table/Query
Control Source (Data)The name of the field in the underlying table/query (should be already set if you've dragged the field onto the form)The name of the field in the underlying table/query (should be already set if you've dragged the field onto the form)
Row Source (Data)A list of the possible values with each value in quotes separated by semicolons. Enter the values here, seaparated by semicolons.The name of the table or query in which values will be looked up. Select the table/query from the drop down.
Bound Column (Data)Always 1Usually 1. This is the column in the Lookup that shared the value used in the original form field.
Column Count (Format)Always 1The number of columns from the Lookup table/query that will be needed.
Column widths (Format)N/aTo hide the coded value in the drop-down, enter 0cm to set the first column width to zero. other width can be set if necesary, separated by semicolons.
Limit to list
Allow Value List Edits

(Data)
These properties determine whether the user can use values not in the list, and whether new values can also be added for future use.

Queries for lookups

Using a query rather than a table for a lookup is particularly poweful as it allows you to:

  • Generate a list using concatenated fields
  • Apply filters to the list
  • Define the sort order of the list (so values are presented in alphabetical order
  • Use selected fields from an existing larger table

Action buttons

The form controls gallery includes the Button control, which can be configured (via a wizard) to carry out a range of actions including opening other objects - for example, a report. This allows you to make forms that enable other users to interact with the database entirely using the form, without needing to work directly with the tables, queries, and reports.

To add a button to review a report:

  1. Open the form in design view, making s ure you can see the controls gallery. Ensure the wizard is enabled.
  2. Select the Button control and click or drag on the form to place the button. The wizard is launched.
  3. There are several options, but to previous a report choose from the Report Operations.
  4. Continue through the wizard to choose the report and name the button. If you choose a button icon add a separate Label control to label the button.
choose the type of operation you want the button to perform

The resulting control will now allow the user to view (and print) a report without needing to know anything about the underlying data or queries.

You can put a button on your form with a corresponding label

Other buttons can be added for other options, like moving between records.

Exercises

Make sure you've downloaded the Essential Access exercise files before attempting these exercises.

Feedback
X