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 can 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 your choices, but it is unlikely to look exactly how you want and will require further editing.

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

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.

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

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.

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

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.

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.

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.

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

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.

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.

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.

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.

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 &

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.

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.

Other expressions

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.

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

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.

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.

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.

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.

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:

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:

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).

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.

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.

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)

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.

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

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.

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.

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