Forms

Forms are designed to simplify the operations performed on individual records of data. Those
operations include entering new records and modifying and deleting existing records. Most of the
operations available through forms can be performed directly on the tables. However, manipulating
data in a large table is much more difficult and error prone.

Forms provide an easy way of selecting individual records, and then display those records in well-
structured layouts, where individual data fields are clearly separated from each other and annotated
with clear labels. The positioning of the data fields and the order of data entry in those fields can be
modified at will, to ensure the most convenient and efficient way of handling data. Lookups can be
created on Foreign Key fields, and fields which contain standardised entries, to simplify the data
entry in those fields. The underlying tables or queries (forms can also be based on queries) can be
easily search for specific pieces of information, and then the records containing that information can
be displayed via forms.

Creating a one-click form

Access provides a number of ways to create different types of forms. The easiest type of form to
create is a one-click form. The screenshot below shows how a one-click form based on the
Membership table contained in the Chemler database, can be created.

First select the Membership table in the

Navigation Pane on the left, then select the
Create tab of the ribbon and click on the Form
icon in the menu.

The next screenshot shows the actual Membership form which was instantly created. One-click
forms are always created in the Layout View. This is a special kind of view, which displays the data,
but it does NOT provide access to that data (data manipulation is done in the Form View), since its
real purpose is to let us redesign the form itself, as it were “from the front”, what is considered as
more intuitive, rather than do it exclusively in the Design View. We will talk about the Layout View in

1
a moment, but first let me explain how records viewed through a form can be selected, added,
modified, deleted and searched for.

The form is instantly created and displayed in the Layout View.

Selecting records in a form

Every form which displays records has a set of record navigation buttons (also known as record
selectors) in the bottom left corner. The figure below explains the function of each of those buttons.
The text field in the middle, which currently shows 3 of 21, can be clicked on, and the index of the
desired record can be entered in it. When the Enter key is subsequently pressed, while the cursor
still remains in the text field, Access moves to the specified record.

Add new record

Go to first Go to last
record record

Go to previous Go to next
record record
Select specific
record

2
Accessing data fields on a form

Generally, there are 3 ways which can be used to access data fields on a form. We could click on a
specific data field, and enter or modify data in that field. Clicking on data fields has the advantage of
allowing us to move randomly from any field to any other field, but clicking on a checkbox will reset
its value. Alternatively, we can press Enter after we have finished entering data in a field. This moves
us to the next data field with the higher tab index (data fields have tab indexes assigned to them,
which determine the order in which Access moves from data field to data field, when for instance,
we are entering data in those data fields and keep pressing Enter after completing each entry).

However, when we are entering text in a Long Text data field, then pressing Enter will move us to
the next line of the entry, rather than to the next data field. Finally, we can use the arrow keys to
move across the data fields on the form, but this is only possible when we are NOT in the process of
entering data in any of those fields. For instance, if we are entering text in a text field, then the
arrow keys allow us to move between the letters of that entry, and only when we have completed
that entry by pressing Enter, will the arrow keys allow us again to move freely between the data
fields.

Adding records via a form

Forms can be displayed in 3 different views which are: Layout View, Design View and Form View. To
manipulate data through a form, we must open that form in the Form View.

To add a new record, we must first move to a new empty record, which will be added after the last
existing record. The easiest way to do that is by clicking on the New Record button in the navigation
set. It is also possible to progress to a new record from the last field of the last record, by pressing
Enter after completing data entry in that last field, or using Down or Right arrow keys to move
beyond the data fields of the last record, to the data fields of the new record. Obviously, those latter
ways of accessing a new record are subject to the limitations described above.

You should NOT enter data in the Auto Number field, because that field is automatically filled by
Access, once you start entering data in any other field. After you have finished entering data in a
field, you may press Enter to progress to the next data field, or you may click on the next field. When
you have entered data in the last field, you can press Enter to move to the first field of the next new
record or click on the New Record button in the navigation set.

You can cancel adding a new record by pressing Escape twice. The first time you press Escape,
Access clears the current data field you have been entering, and next time clears the entire record.
The Auto Number is increased each time you do that. When you move to a different record at any
stage of adding a new record, then Access saves the new record, and it is NO longer possible to
cancel it, but it can only be deleted.

Editing data in a form

To edit data in a record you must first move to that record. You can use the navigation buttons for
that purpose or you can search for a specific piece of information which the record contains (we will
talk about searching records in a moment). The process of editing data in data fields is NOT much

3
different from the process of entering data described above. While a record is being edited a little
pencil sign replaces the arrowhead, which otherwise appears on top of the record bar at the left
margin of the form (see the screenshots below). The editing mode is terminated only after you move
to another record.

Deleting records through a form

To delete a record, you must first move to that record and then select it, by clicking on the record
bar at the left margin of the form. Following that, the record bar becomes highlighted and you can
delete the record by pressing Delete on the keyboard, or clicking on the Delete icon in the Home
menu.

4
Searching for records

Every form which displays records has a search facility, which is located at the left side of the bottom
bar, after the navigation buttons and filters. To search for a particular piece of information in the
underlying records, you input that piece of information in the search field, and without leaving that
search field you keep pressing Enter. Each time you press Enter the form displays a different record
containing that information. For instance, if I entered the value of 15 in the search field, and without
leaving that search field kept pressing Enter, then the form would display every record which has 15
included in the value of any of its data fields. The screenshots below show two of such records.

5
Filtering and sorting forms

Forms can be sorted and filtered in vary much the same way as tables. Filtering allows you to reduce
the number of records, so you are left with only those records which interest you. Filtering and
sorting records is NOT part of the City & Guilds assignments, so I am NOT going to discuss it.

Printing forms

Printing forms is possible, but it is NOT viewed as essential, so unlike reports, forms cannot be
opened directly in the Print Preview view. Instead, we have to first select a form in the Navigation
Pane or open it, and then choose File → Print → Print Preview. The screenshot below shows how
the Membership form we have created would look in Print Preview. Clearly, the widths of the text
fields would have to be reduced, to ensure that each record fits in the width of the page. All the
records would be printed on several pages. The form heading would be printed only on the first
page, and the date and some summary information would be printed at the bottom of last page.

One of the City & Guilds assignments stipulates that a form should be printed with full details of its
exterior and it should be free standing, i.e. released form the Access work and display area. This can
only be done by converting a form to the Pop Up mode and taking a screenshot of the form. To
convert a form to the Pop Up mode, the form must be opened in the Design View and must be
selected, and then the Pop Up property of the form must be set to Yes. The Pop Up property can be
found in the tab Other of the Property List. The two screenshots below show how the Pop Up
property of the Membership form has been set to Yes, and how the form looked like in the Pop Up
mode.

6
 Open the form in the Design View and select it by
clicking on the little square in the top left corner
of the form, then open the Property Sheet and
set the Pop Up property of the form to Yes.

Different types of forms

As I said earlier, Access allows to create a number of different types of forms. One of those types is
the one-click form, and we have already demonstrated how such form can be created. Other types
of useful forms can be created with the help of the Form Wizard. Unlike one-click forms, which are
created with the built in Layout, the forms created by the Form Wizard do NOT use the Layout.
There are two particularly useful types of forms, which the Form Wizard can create, and those types
are the Columnar forms, which display one record at a time, with the data fields arranged in
columns, and Tabular forms, which display multiple records arranged in rows, where the data fields
of each record are arranged sequentially in a single row.

7
For the purposes of the City & Guilds assignments we will be mostly interested in 2 types of forms,
which are the one-click form and the columnar form created by the Form Wizard. The screenshots
below demonstrate how a columnar form can be created for the Membership table.

1. Select the table Membership in the Navigation Pane, open the Create menu of the ribbon and
click the Form Wizard icon in that menu. The first interface of the Wizard allows us to choose
any Table/Query for which the form should be created, by selecting that table or query from the
dropdown list. Once a table or query has been selected, the Wizard displays all the data fields of
that table or query in the Available Fields area. We can now use the little buttons annotated
with arrowheads to move some or all of those data fields to the Selected Fields area. Note that
the table Membership was selected in the Wizard’s interface right from the start, since we
selected that table in the Navigation Pane, prior to activating the Wizard.

2. Transfer all the data fields to the Selected Fields area and click Next.

3. Choose the Columnar type of form and click Next.

8
4. Choose a title for the form and click Finish.

5. The Columnar form which the Wizard has created is shown below. Note that all the data fields
are arranged in a single column, and since that column is very tall, some data fields at the
bottom of the column are NOT visible. We need to use the scroll bar at the right side of the form
to display those data fields.

9
Before we change the subject, let us have a quick look at some other types of forms which the Form
Wizard could create for the table Membership.

The screenshot below shows a Tabular form, which displays multiple records, with each record
occupying a single row. This type of form is clearly unsuitable for the table Membership, since that
table contains too many data fields, and consequently, all those data fields appear excessively
squashed in their rows.

The screenshot below shows another type of form, which the Wizard calls Justified. The form shows
one record at a time. The sizes of the labels and data fields are arbitrarily chosen and justified to fit a

10
rectangular grid, hence presumably the name Justified. The data fields, the data inside them, and the
labels are all clearly displayed, but NOT easy to see, since the grid is complex an irregular.

Using the Layout

The layout is an invisible grid consisting of columns and rows. When one-click forms are created,
that grid is tailor made to fit all the controls of the form (the term controls applies to labels and data
fields jointly). The controls fill the cells of the grid provided for them. The columns have a fixed width
and the rows have a fixed height, so all the controls in a given column have the same width and all
the controls in a given row have the same height. The initial arrangement of the controls reflects the
arrangement of the data fields in the underlying table, but that arrangement can be later modified.

The arrangement of the controls in the Layout and the Layout itself can be modified while the form
is opened in the Layout View or Design View. I prefer to do those modifications in the Design View,
so I will use the Design View to demonstrate how those modifications can be done.

There are 2 basic modifications which can be done to forms using the Layout, and those
modifications are: to change the arrangement of the controls contained in the Layout, and to change
the widths of the columns and the heights of the rows.

Rearranging controls in the Layout

The position of a control in the Layout can be changed by dragging that control to its new position.
The controls can be dragged individually, or a group of controls can be selected and dragged jointly
to their new positions. To select a group of controls, you keep the Ctrl key pressed and click on each
of the controls. When a control is dragged to a position which is already occupied by a different
control, Access creates space for that dragged control, by inserting an additional row in the grid. The
screenshot below demonstrates what happened when I tried to swap the positions of the data fields
Surname and Forenames.

11
1. I dragged the data field Forenames to the position above the data field Surname. Access inserted
an extra row in the grid to accommodate the data field Forenames in its new position.
Surprisingly, the label Forenames has been moved alongside the data field Forenames, but I
definitely saw data fields and labels dragged independently of each other.

2. Another way of moving the data field Forenames above the data field Surname is to select that
field and then click on the Move Up icon in the Arrange menu of the ribbon. This time the data
field Forenames has clearly been moved independently of the associated label, and I could later
drag the label Forenames individually and place it alongside the relocated data field.

12
It seems that swapping the positions of the Surname and Forenames controls caused a considerable
disruption of the grid, and more shifting of controls is necessary to restore the previous order. The
next screenshot demonstrates how multiple controls could be selected and jointly dragged to their
new positions.

3. I first selected the highlighted controls by holding the Ctrl key down and clicking on each of
them, and then I dragged them jointly to their new positions. Note that an empty row has now
appeared at the bottom of the grid, what makes the grid unnecessarily oversized. To delete that
row, I had to select all the controls in that row and delete them jointly.

In view of the considerable disruption of the grid and numerous shifts of the controls, which were
necessary to restore it back to order, it seems that we could swap the positions of the Surname and
Forenames controls (data fields and the labels) faster and with less effort, if we first deleted the
Surname controls from the grid altogether, then moved the Forenames controls in their place, and
finally reinserted the Surname controls by dragging them from the Field List (see page 45) to the
space left by the Forenames controls.

Resizing columns and rows of the Layout

Columns and rows of the Layout can be resized in two ways. Individual controls can be selected and
their edges can be dragged horizontally, to modify widths of the columns, or vertically, to modify
heights of the rows, or the dimensions of the selected controls can be modified from inside the
Property Sheet (see page 18). Since, all controls in a column have the same width, and all controls in
a row have the same height, so when we change the width or height of an individual control in a
particular column or row, we effectively change the width of that column and all controls in that
column, or the height of that row and all controls in that row.

As mentioned above, the alternative way to resize the columns and rows of the Layout, is to modify
the dimensions of individual controls placed in those columns and rows from inside the Property
Sheet. This implies resetting the Width and Height properties of the selected controls inside the
Property Sheet.

13
Other ways of manipulating the Layout

Additional operations can be performed on Layouts, such as Inserting extra rows and columns or
Merging/Splitting cells. Those operations can improve the appearance of forms and make them
more readable, but since those operations have NO relevance to the City & Guilds assignments, I will
NOT discuss them in this paper.

Releasing controls from the Layout

Individual controls or groups of controls can be selected and released from the Layout, or in the
extreme case all controls can be selected and released from the Layout. The controls which were
released from the Layout have unrestricted movement and can be moved freely to any position on
the form.

All controls, which we want to release from the Layout must be first selected. We have already
shown how individual controls and groups of controls can be selected. To select all controls on the
form we can first click on any of those controls, and then the outline of the grid is displayed and a
little square annotated with a 4-directional arrow appears at the top left corner of the grid. When
we click on that little square, all the controls in the grid become selected. This is illustrated by the
screenshots below.

When the encircled little square is clicked, all the controls in the Layout become selected.

Once the desired controls have been selected, we can release them from the Layout, by wright-
clicking the selected controls and choosing Layout → Remove Layout from the displayed menu, or
we can open the Arrange menu of the ribbon and click on the Remove Layout icon in that menu.
Those two options are illustrated by the screenshots below.

14
Right click the selected controls and select Layout → Remove Layout from the displayed menu.

The Remove Layout icon in the Arrange menu of the ribbon was clicked. All controls of the form are
now liberated from the Layout.

Arranging freestanding controls on the form

Some forms, like the Columnar form created by the Form Wizard, do NOT use the Layout, and their
controls are freestanding from the start. Such controls can be moved freely around the form and

15
resized individually. However, there is a price we pay for that freedom, since it takes much more
work and time to arrange such controls decently on the form.

In fact, we may struggle to emulate some features of the Layout, for instance making sure that a
group of fields has the same width, or is aligned evenly in the horizontal direction, or is spaced
evenly in the vertical direction.

To move a freestanding data field or a label to a new position on the form, we click on that data field
or label to select it, and then drag it to that new position. However, freestanding data fields and
their labels are always moved together. To move a data field or a label alone, we must select it and
drag it by its handle. The handle of a data field or a label is a tiny little square at top left corner of the
rectangle representing that data field or label, as illustrated below.

To move a group of data fields and their labels to a new position on the form, we select those data
fields and labels and drag them to that new position. Selecting free standing data fields is easy, since
we click on the form anywhere near the data fields to be selected and then drag the mouse pointer
over those data fields. The mouse pointer traces a rectangle (see below), and any control which is
even partially caught inside that rectangle, will be selected.

16
Let us assume that I want to drag the selected address-related data fields and their labels (see
above) to a new position near the top of the form and create a second column of labels and data
fields beginning to the right of the Membership No data field. However, I do NOT want to increase
the width of the form, which may later be printed, so I decide to reduce the width of all labels first,
before I move the selected fields.

This can be done in two ways: either I select all the labels and reduce the width of one of those
labels by dragging its right edge to the left, what will reduce the width of all the labels, or I reduce
the Width property of all the selected labels in the Property Sheet. In either case, this leaves a gap
between the modified labels and the corresponding data fields (see the screenshot below), and now
I must reduce that gap.

Note that I cannot simply select all the data fields and move them closer to the labels, since the
labels would move alongside the data fields and the gap would NOT be reduced. In fact, there are
only 2 ways in which a column of controls can be moved closer to, or further away from a column of
associated controls, and that can be done either via manipulating the alignment of the column which
must be moved, or via jointly resetting the Left property of the controls in that column. Those 2
ways of shifting an individual column of controls horizontally (without moving the associated
controls), are explained further below.

17
 Reduce the width of any of the
selected labels or reduce their
Width property

The first way to reduce the gap is to select one of the data fields and drag it by the handle towards
its modified label and drop it near that label. Then select all the data fields (including the one which
has been moved) and choose the Align → Left option from the Arrange menu of the ribbon (see the
1st screenshot below), or right-click the selected data fields and choose the Align → Left option from
the displayed menu (see the 2nd screenshot below).

18
The second way to reduce the gap is to select all the data fields (without moving anyone of them)
and then reduce the Left property of those data fields in the Property Sheet (the Left property of a
control determines the distance between the left edge of the form and the left edge of that control).

Following all those preparations I was finally able to move the address-related data fields and labels
to the top of the second column, but NOT before I reduced the widths of some particularly wide
data fields, such as Surname, Forenames, Street and even Town. This is illustrated by the screenshot
below.

19
At this point I might decide that the 2 columns of labels and data fields are excessively squashed,
and abandon the idea of keeping the width of the form unchanged. However, if I decide to increase
the width of the form, I will face the prospect of increasing the gaps between the labels and data
fields in those columns.

To increase the gap between the first column of labels and data fields, I could first select all the
controls on the form (see below) and move them all jointly to the right. Following that I could move
the first column of labels to the left, by realigning it to the left or decreasing the Left property of all
the labels in the Property Sheet, as described above. I could then reposition different groups of
controls and modify horizontal gaps between different columns of controls using the combination of
dragging, horizontal realigning and resetting the Left property in the Property Sheet, as appropriate.

All controls were selected and moved to the right, so the 1st column of labels can be subsequently
shifted to the left.

20
Vertical alignment does NOT seem to be as useful as horizontal alignment. Consistently with
horizontal alignment, vertical alignment operates on labels and data fields independently of each
other, so if labels and data fields should be aligned jointly, then all those labels and data fields must
be selected prior to the alignment. Vertical alignment operates well on individual controls or pairs of
linked controls, but when columns of controls are aligned upwards or downwards, then the spaces
between the rows of those columns are lost. Those columns of controls must be subsequently
selected, and vertical spacing between the rows of those columns must be increased. All the
alignment options and spacing options can be accessed from the Arrange menu of the ribbon, by
clicking respectively on the Align icon or Size/Space icon (see below).

Reports
Reports are primarily designed for viewing and printing of specifically chosen information, which
may originate from one or more database tables. In a sense, reports function like queries, which also
allow to select some specific information from multiple database tables, to meet some specific
needs of database users. Like queries, numerous reports, which select different combinations of
data, can be saved and run whenever a particular combination must be viewed or printed again.
However, reports have a clear advantage over queries, in that they allow to display the selected
information in well-structured layouts, where some large data items are fully visible, and they also
allow to group and summarise information, what makes that information easy to understand.
Reports also facilitate printing of data by arranging data into printable formats.

One-click reports

As with forms, Access allows to create a number of different types of reports. The easiest type of
report to create, is the one-click report. To create such report, we select in the Navigation Pane a
table or query on which the report must be based and then click on the Report icon in the Create
menu of the ribbon. This is illustrated by the screenshot below.

21
The next screenshot below shows a one-click report, which has been based on the table
Membership. The report is initially displayed in the Layout View. The title of the report is
Membership, which is the same as the name of the table on which the report has been based. The
report is tabular, what implies that the data fields representing each record are arranged
sequentially in a single row. The order of the data fields in each row corresponds to the order of the
data fields in the underlying table. The report is very wide and it does NOT fit in the width of a single
page. We can tell that, since Access displays the page breaks in the Layout View.

Vertical page break

Different sections of a report

The next screenshot below, shows the report Membership opened in the Design View. We can see
that the report consists of a number of sections. The Header section appears only once at the start

22
of the report and the corresponding Footer section only once at the end of the report. The Header
contains the repot title, which is the same as the name of the underlying table, and the current date
and time. The Footer contains the count of displayed records.

The Page Header and Page Footer sections appear respectively at the start and the end of every
page. The Page Header contains all the data field labels, which are arranged sequentially in a single
row. The Page Footer displays the page numbers.

The Detail section is the heart of the report, which contains all the data fields arranged sequentially
in a single row and in alignment with the labels. In the Report View the Detail section is displayed
repeatedly, each time forming a new row of data fields representing a different record. The rows fill
the middle part of each page, and new pages are generated until all the records have been added to
the report.

It should be noted, that any report always has a single Detail section, and that section is always the
middle section of the report, while all other sections come in pairs of matching Headers and Footers,
which are placed concentrically around the Detail section. The Detail section of the report below is
nested between the Page Header and Page Footer, and those 3 sections are further nested between
the Report Header and Report Footer.

Any pair of Headers or Footers can be added or deleted depending on its usefulness for the report.
To add or delete a Header and Footer as a pair, right-click on the bar of any section of the report
opened in the Design View and select or deselect that pair (see below). Alternatively, the height of
any Header or Footer section can be reduced individually to zero, so for instance, we can preserve
only the Header section and shrink the unused Footer section to nothing, or the other way around.

To increase or decrease the height of a particular section of the report, you drag up or down the bar
of the neighbouring section, which lies immediately below that section. To do the dragging, you
place the mouse pointer at the top edge of the bar to be dragged and wait until the pointer has

23
changed its shape to a tiny vertical two-directional arrow crossed in the middle by a tiny horizontal
line, what looks something like that:

Viewing a report in Print Preview

Reports can be opened in 4 different views which are: Report View, Print Preview, Layout View, and
Design View. Print Preview gives us an instant indication of how a report would look like, if it was
printed. As we said earlier, the Membership report is very wide and it does NOT fit in the width of a
single page. Looking at the Print Preview of that report we can see, that the report would occupy the
widths of nearly 3 pages, if it was printed in the Portrait layout, and 2 pages in the Landscape layout,
and even though, the last 2 data fields of the underlying table, which are Smoker and Sex, would NOT
be included in the report. This is illustrated by the 3 screenshots below.

The screenshot above shows the entire report displayed in the Portrait orientation. Vertically the
report fits in the height of a single page, but horizontally it spreads over the widths of 3 pages.

24
 Sporting Interests is the
last column of the report,
while Smoker and Sex
columns are missing.

Clearly, the Membership report is too wide for comfortable viewing and printing. Preferably, reports
should fit the width of a single page. Obviously, we could modify the Membership report by deleting
some of its columns, but generally it is better to build reports based on queries rather than directly
on tables. Queries allow us to select and sort only the information we need, but importantly they
also give us the opportunity to select that information from multiple tables.

Different types of reports

As I said earlier, Access allows us to create a number of different types of reports. One of those types
is the one-click report, and we have already shown how such reports can be created. One-click

25
reports are created with the built-in Layout. The rules which apply to the structure of that Layout,
the modifications which can be made, and the way of releasing the controls from it, have already
been discussed in the context of forms, and I am NOT going to repeat that discussion again.

Other types of useful reports can be created with the help of the Report Wizard. In contrast to one-
click reports, the reports created by the Report Wizard have freestanding controls. Reports created
by the Report Wizard may, or may NOT involve grouping (the meaning of grouping will be explained
in a moment).

Reports which do NOT involve grouping

Reports which do NOT involve grouping, are essentially NOT different from printed forms. There are
3 types of those reports, which the Wizard calls Columnar, Tabular and Justified, and they match
exactly the corresponding types of forms, but unlike some forms, which show only individual
records, the reports always show all the records arranged in a vertical sequence.

The 1st screenshot below shows the Justified type of report based on the Membership table in the
Report View (in other words the Data view). As you can see, the report begins with a single Report
Header followed by a column of rectangular grids, were each of those grids contains all the labels
and data fields of a different record.

The 2nd screenshot below shows the same report in the Design View. We can confirm that indeed
the report begins with a single Report Header section, the Page Header and Report Footer sections
of the report have been shrunk to zero, the grid is placed in the Details section of the report, what
explains why the grid is displayed repeatedly for every record, and the Page Footer section is shown,
and it contains the date and the page numbers.

26
Exactly the same arrangement of the Header and Footer sections can be found in the Columnar
report based on the Membership table, except that the grid in the Detail section of the repot is
replaced by a column of all labels and data fields of a single record.
The Tabular report differs significantly from the above two types of reports, in that it contains a
proper Page Header section, which holds all the labels arranged sequentially in a single row, while
the Detail section of the report holds all the data fields of a record, also arranged sequentially in a
single row and in alignment with the labels. This is illustrated by the screenshot below.

27
Reports which involve grouping

1. All types of reports created by the Report Wizard are initiated in the same way. First, we select
in the Navigation Pane a table or query on which the report should be based, then we open the
Create menu of the ribbon and click on the Report Wizard icon in that menu. This activates the
Report Wizard, and the 1st interface of that wizard is displayed (see below).

The 1st interface of the Report Wizard contains a combo box named Tables/Queries and two
text areas named Available Fields and Selected Fields. The combo box allows us to select a table
or query on which the report should be based. Once we make that selection, all the data fields of
the selected table or query appear in the Available Fields area. We can now use the little
buttons annotated with arrowheads to move some, or all of those data fields to the Selected
Fields area. Note, that the Membership table was selected in the Wizard’s interface right from
the start, since we selected that table in the Navigation Pane prior to activating the Wizard.

This time I have transferred only 6 data fields to the Selected Fields area (see below).

28
2. The 2nd interface of the Wizard (see below) allows us to select the fields on which the grouping
should be done. Access has already suggested that the Category No is a suitable field for
grouping, and I am going to go along with that. Grouping implies that all records which have the
same value in the grouping field will be put together in separate sections of the report. In a
moment you will see how this is done in practice.

Note that grouping can be done on more than one field. For instance, I might include Sex as the
2nd grouping field (see below). In such case, there would be 2 levels of grouping, what implies
that records would be primarily grouped by Category No, and then in each of those groups there
would be sub-groups for the values of Yes and No representing different sexes.

29
 Alternatively, I may decide that I do NOT want to use grouping at all, and remove all grouping
fields (see below). As we see later, that decision would have an impact on the choice of reports
that the Wizard could create.

3. The 3rd interface of the Wizard allows us to decide how the records in each category should be
sorted (see below). I decided that the records should be sorted on the Surname and Forenames
fields, in an ascending order.

30
4. The 4th interface of the Wizard (see below) allows us to choose which type of report layout we
want. There are 3 available choices. We can choose Stepped, Block or Outline layout. We can
also choose the orientation which will be applied when the report is printed. The choice is
between the Portrait and Landscape orientation. Let us initially choose the Stepped layout,
which is probably most commonly used, and later we can opt for the other layouts to see how
they differ.

Note that the choice of available reports would be different if I decided NOT to use any grouping
fields. The screenshot below shows the available report layouts after I went back a couple of
steps and deselected Category No from the list of grouping fields.

31
5. The final interface of the Wizard allows us to select the name for the report.

The next screenshot below shows the Stepped report Membership4, which the Report Wizard has
created. The report requires some adjustments. The Wizard has rearranged the fields, so the fields
which were used for sorting are shifted to more prominent positions on the left side of the report.
The field Membership No has been squashed due to lack of space, and for some reason the Wizard
has failed to include the field Sex in the report. However, the effect of grouping is clearly visible.

The Category No label and Category No data field are moved to the left edge of the report, while all
other fields are shifted to the right, what creates the effect of indentation or stepping. Each category
number appears only once in the report, while different numbers of records lie in between different
category numbers. The way how this effect has been achieved becomes evident, when we look at
this report in the Design View.

32
The following screenshot shows the report Membership4 in the Design View.

We can see, that there is another section added to the report, which corresponds to the grouping
field Category No. Only the Category No Header is visible, while the Category No Footer is hidden,
since the report Membership4 has NO need to use it. However, the Category No Footer could be
made visible (see page 37 to find out how this can be done), if for instance, some summary
information related to each category had to be displayed in that footer.

The Category No Header and Category No Footer are the most inner pair of sections, in which the
Detail section of the report is nested. However, if additional grouping fields were used, then an
additional pair of Header and Footer sections would be added for each of those fields, and those
additional pairs of Headers and Footers would be nested within each other in such way, that more
inner levels of grouping would correspond to more inner levels of nesting. This is illustrated by the

33
screenshots of the report Membership4a (see below), which is grouped primarily on Category No
and secondly on Sex.

We can see that two extra headers have been added to the report (one per each grouping field), and
that the Sex Header is nested within the Category No Header, since Sex represents the 2nd level of
grouping.

The Page Header section of the report Membership4a contains all the labels arranged sequentially
in a single row, the Category No Header contains only the Category No data field, the Sex Header
contains only the Sex data field, and the Detail section contains all the remaining data fields. The
data fields in all those sections are aligned with their corresponding labels in the Page Header.

34
It is important to understand how nested grouping works. To avoid excessive complication, let us
consider only 2 levels of grouping, and let us relate our discussion to the specific example of such
nested grouping, which can be found in the above report Membership4a. The Category No Header,
which represents the 1st level of grouping, displays every value the Category No data field (i.e. every
category number) only once, while the Sex Header, which represents the 2nd level of grouping,
displays all the values of the Sex data field (i.e. Yes and No) repeatedly, once per every value of
category number. The Detail section puts multiple records in every section of the report denoted by
a specific value of Category No and Sex, but those records always contain the matching values of
Category No and Sex.

Before we move to the next subject, let us briefly look at the other report layouts that the Report
Wizard could create, as alternatives to the Stepped report Membership4 we created earlier. The 1st
screenshot below shows the Block layout and the 2nd screenshot the Outline layout for the same
selection of 6 data fields and the single grouping field Category No.

Each value of
Category No
appears in line with
the 1st record in
that category
rather than above
the 1st record, as in
the Stepped report
Membership4 on
page 33.

The Category No
label appears in
line with each
Category No value
above the 1st
record of each
category.

35
Using the Group, Sort, and Total pane

The existing groups and their sorting can be adjusted, or entirely new groups can be created using
the Group, Sort, and Total pane, which occupies the bottom part of the Design View of a report,
once the Group & Sort option is selected in the Design menu of the ribbon. This is illustrated by the
screenshot below.

The Group, Sort, and Total pane.

The Group, Sort, and Total pane contains numerous combo boxes arranged in horizontal lines,
where each line corresponds to an existing group of the report, or sorting of a group. The
indentation is used to distinguish between different levels of grouping and sorting, where more
indented lines imply deeper sublevels. At the far right-end of every selected line (see below) there is
an up-pointing arrow, down-pointing arrow, and an X symbol. The arrows can be clicked to move a
line respectively up or down, and the X symbol to delete the line. At the bottom of the pane there
two buttons with invisible borders (see below), which can be clicked to Add a group or Add a sort.

The combo boxes included in each line allow the user to make different choices which modify the
grouping or sorting feature that the line represents. At the end of each line of combo boxes there is
a little button named More, which the user must click to gain access to the full range of combo
boxes included in the line. The screenshot below shows the full range of choices for the line which
corresponds to the group Category No in the Stepped report Membership4 shown above.

36
The combo boxes provided inside the Group, Sort, and Total pane, allow you to make the following
choices:

Group on… – To select the field which is used for grouping.

from smallest to largest – To sort the grouping field in the ascending or descending order.

by entire value – To select an increment, by which the range of values for each subsequent group of
a grouping field would be increased. For instance, if the grouping field was the product price and we
selected the increment of 5 then the price ranges for the subsequent groups would be 0-4.99, 5-
9.99, 10-14.99 and so on.

with totals/with no totals – To calculate group subtotals on any numeric fields. You can count
values, calculate sums and averages, determine maximum and minimum values in a group. You can
place the text box controls, in which those calculations are contained, inside a group header or
footer. Finally, you can include the grand total of all the group subtotals in the report.

with title – To specify the text for a label, which is placed inside the group header before the data
field displaying the value for each group, so that text appears before every such value.

with/without a header section – To add or remove the header section of a grouping field.

with/without a footer section – To add or remove the footer section of a grouping field.

keep whole group/do not keep group together on one page – To decide whether groups should be
kept together on each page of the printed report, or whether they could be split between different
pages.

Sort by… – To sort the records within a group on a specific field.

37
Adjusting a report created by the Report Wizard

The City & Guilds assignments stipulate that you create a report based either on a query (which you
were earlier instructed to create) or on an existing table, and group that report on a specific field.
The Report Wizard could be used to create a Stepped report with a single level of grouping, which
meets those requirements. However, such report will require some adjustments.

I could demonstrate which adjustments would be necessary, and how those adjustment could be
implemented, on the example of the Stepped report Membership4, which we created above (see
page 33), and actually that report would be the closest to the reports requested by the City & Guilds
assignments. But to demonstrate the full range of the possible adjustments, I will begin with a
Tabular report, which has the same data fields as the report Membership4, but does NOT involve
any grouping. In the initial stage I will transform that report to the equivalent of the report
Membership4, and afterwards I will continue to adjust the actual report Membership4. The two
screenshots below show the Print Preview and the Design View of the Tabular report Membership7,
which I created for the purposes of that demonstration.

38
Adding a group to an existing report

I will first demonstrate how the above report can be grouped on Category No. There are 2 ways in
which a group can be added to a report. The 1st way is to right-click on the data field to be used as
the grouping field, and select Group on from the displayed menu. The same menu can be used to
sort the grouping field in the ascending or descending order. The 2nd way is to click on the Add a
group button in the Group, Sort, and Total pane, and select the grouping field from the list of the
available data fields. The two screenshots below illustrate how a group based on the Category No
data field could be added to the above repot, using each of those 2 approaches.

39
 The Add a group button was clicked here

No matter which approach we choose, the report is modified in exactly the same way, namely, the
Category No Header is inserted in the report. This is illustrated by the screenshot below. A slight
difference appears in the Group, Sort, and Total pane, since with the 1st approach the Category No
group shows as the top line in that pane, and with the 2nd approach as the bottom line, but this can
be easily rectified by clicking twice the up-pointing arrow at the far right of that line.

The subsequent adjustments are as follows:

1. I first moved the Category No data field to the Category No Header. The most reliable way to
move a control between different sections of a report is to cut and paste it in its new
position. Dragging a control to a different section of a repot is sometimes impossible. To

40
 paste the Category No data field inside its Header, I clicked on the bar of that Header and
pressed Ctrl+V on the keyboard. This is illustrated by the screenshot below.

2. Next, I had to move the Category No label to the far left of the report. To do that, I first cut
the Category No label from its position, then moved the labels Surname, Forenames and
Membership No to the right, to fill the empty space left by the removal of the Category No
label, and then pasted the Category No label back in the Page Header. This is illustrated by
the 1st screenshot below. Following that, I aligned all the data fields with their repositioned
labels, by selecting those data fields and moving them using the arrow keys. I also reduced
the height of the Category No Header. This is illustrated by the 2nd screenshot below.

41
 3. Following the above described adjustments, the Tabular report Membership7 (see below)
closely resembles the Stepped report Membership4. From now on, I will use the report
Membership4 to demonstrate any further adjustments, since as I said earlier, you are most
likely to create a similar report, to address the requirements of the City & Guilds
assignments, and you will face the need for similar adjustments.

Rearranging freestanding controls in reports

The Stepped report Membership4 is shown in the Print Preview and Design View on page 33. The
main shortcomings of that report are that the columns of data are NOT evenly distributed and the
column of Sex is missing altogether. To rectify the first problem, I had to reduce the width of some
data fields and the associated labels, and then reposition those data fields in alignment with the
labels.

The labels and the associated data fields move independently of each other when they are located in
different sections of the report, but they move jointly within the same sections. Multiple labels and
data fields can be selected by dragging the mouse pointer over multiple sections of the report. The
mouse pointer then traces a rectangle, and any controls included even partially in that rectangle
become selected. Alternatively, it is possible to select multiple controls by clicking on each of those
controls, while simultaneously keeping the Ctrl key pressed down.

The widths of multiple controls can be reduced jointly by selecting those controls and dragging the
edge of one of those controls to reduce its width, or by reducing the Width property of all the
selected controls in the Property Sheet. In either case, the widths of all the selected controls become
reduced. Access is often excessively generous when setting widths of various report controls, so
some widths can be safely reduced, without any adverse impact on the report. For instance, the
width of the Category No controls in the report Membership4 is the same as that of the Surname

42
and Forenames controls, but Category No is a numeric data field and its size is set to Byte (an integer
in the range 0-255), while Surname and Forenames are textual data fields and their sizes are set to
30 characters each.

In any case, we can always verify the effect of the report controls being repositioned or resized by
looking at the report in the Report View or Print Preview, and then implement the necessary
corrections. Even if the Surname data field has the size of 30 characters to accommodate some very
long surnames, but the longest surname which features in a report is only 10 characters long, then
we can safely reduce the size of that field by more than a half. The widths of data fields containing
long stretches of text (such as Sporting Interests) can be reduced without the fear of truncating
some of that text, if we set the Can Grow property of such data fields to Yes in the Property Sheet.
With that arrangement in place, Access displays long stretches of text on multiple lines, if those
stretches are too long to fit in the reduced widths of their data fields. In fact, Long Text data fields,
such as Sporting interests, have the Can Grow property set to yes automatically, however, Short Text
data fields do NOT, and labels cannot grow at all, so long data field names, which cannot fit in the
widths of their labels, are always truncated.

Finally, one useful tip, which can be helpful when you try to improve the distribution of data
columns in a report, and better align those data columns with the corresponding labels. You can
modify the alignments of those data fields. Access uses fixed alignments for different types of data
fields. For instance, numeric data fields and dates are always right-justified and textual data fields
are always left justified. To change those alignments, select a particular data field, open the Format
menu of the ribbon and choose the required alignment from that menu. Alternatively, you can
modify the Text Align property of the selected data field in the Property Sheet.

Using a combination of the above described techniques, I manage to improve the distribution of the
data columns in the report Membership4, and created some space at the right side of that report,
for the inclusion of the missing Sex field. This is illustrated by the screenshots below.

43
Adding an extra field to a report

To add an extra field to a report, first open that report in the Design View, then open the Design
menu of the ribbon and select Add Existing Fields from that menu. This is illustrated by the
screenshot below.

Next, select the desired data field from the displayed Field List and drag and drop it in the available
space in the Detail section of the report. The screenshot below shows how the Sex data field was
dragged and dropped inside the Detail section of the report Membership4. Note that the checkbox
representing the Sex data field is accompanied by the corresponding label.

44
The next step is to cut the Sex label from its current position in the Detail section of the report, and
drop it inside the Page Header of the report. This is illustrated by the screenshot below.

The final step is to align the Sex label with the Sex checkbox. This can be done by selecting the label
(if it is NOT already selected) and moving it with the help of the arrow keys. The 1st screenshot
below shows the report Membership4 in the Design View, after the Sex label has been repositioned
and aligned with the checkbox, and the 2nd screenshot shows that report in Print Preview, with the
Sex data column appended to its right side.

45
Improving the presentation of reports

The presentation of reports can be improved by applying different fonts and different font
properties (such as size, weight, colour etc.) to some chosen report controls (such as report titles,
labels and data fields), or by the inclusion of additional controls such as pictures or lines.

Changing font or font properties of a control

To change the font or font properties of a control, first select that control in the Design View of the
report, then open the Format menu of the ribbon and choose the desired font and font properties

46
from that menu. The screenshot below shows how the title of the report Membership4 can have its
font set to Microsoft San Serif and the size of that font to 20, using the above described method.

Alternatively, open the Design menu of the ribbon, display the Property Sheet and set the Font
Name property of the selected report title to Microsoft San Serif and the Font Size property to 20.
This is illustrated by the screenshot below.

Inserting a picture in the report header

To insert a picture in the report Header, first open the report in the Design View and create space
for the picture by moving the report title, increasing the height of the header, etc. Then open the

47
Design menu of the ribbon and click on the Image control in the Controls section of that menu (that
section is alternatively called the Tool Box). This is illustrated by the screenshot below.

Next, click on the report Header, at the point where the picture should be inserted, and drag the
pointer of the mouse to define the outline of the picture (see below).

When you finish dragging and release the mouse button, the Insert Picture window appears, which
allows you to search for the picture which you want to insert (see below).

48
Locate the picture you want to insert in the report, select it, and click OK (see below).

The picture is instantly inserted in the frame of the image control (see below). You may adjust that
frame, if necessary, to fit tightly around the image.

49
The screenshot below shows the report Membership4 in Print Preview, with the picture inserted in
its Header.

There is also another way, how you can insert a picture in the report header. The main difference is
that you do NOT select the image control from the Tool Box, but instead you click on the Insert
Image button in the Design menu of the ribbon. This allows you to reuse a previously used picture,
or browse for a new one.

Once you found and selected the picture you want to insert, move the mouse pointer (which now
has the shape of a little rectangle with a picture inside it} over the report header, click at the point

50
where you want the picture to be inserted, and drag the pointer to define the outline of the picture
(see below). The picture is instantly inserted, when you finish dragging and release the mouse
button.

Inserting lines in reports

To insert a line in a report, click on the Line control in the Tool Box, then click on the report, at the
point where the line should begin, and drag the mouse pointer to the point, where the line should
end. This is illustrated by the two screenshots below.

The screenshot below shows the line inserted in the Category No Header. Once the line has been
inserted, it is possible to manipulate its properties, such as Width, Height (height of a line is zero
when the line is perfectly horizontal), Border Width (line thickness), Border Color (line colour), etc.

51
The screenshot below shows the report Membership4, with each category section separated by a
line.

Properties often used with reports

When a report is open in the Design View, a section of the ribbon named Report Design Tools
appears highlighted in a darker shade of red (see below). That section consists of 4 subsections,
which are named Design, Arrange, Format and Page Setup. The menus of those 4 subsections
include some very useful features, and we have already shown how some of those features can be

52
used. It would be useful to discuss the purpose of all the features provided in those menus, but then
this document would never end.

One very useful feature, which is available in the Design menu of the ribbon, is an icon which allows
us to open the Property Sheet. The Property Sheet can be used to manipulate the properties of any
selected part of the report, starting from the report itself (incidentally, to select the report as a
whole, you must click on a little square in the top-left corner of the report, which is encircled in
above screenshot), then moving to every section of the report, and ending on every individual
control in every section.

The table below shows a list of properties, which are frequently used to manipulate various parts of
reports.

Selected part Property Description

Label or data field Width Allows to set the width of a selected control or a group
of selected controls.
Label or data field Left Allows to reposition a selected control or a column of
selected controls horizontally in relation to the left
margin of the report, without simultaneously
repositioning the associated controls.
Label or data field Text Align Allows to change the default alignment of the value
displayed inside a control.
Data field Can grow When set to Yes, the height of a data field can grow to
accommodate more lines of text, without increasing the
width of that data field.
Detail Keep Together When set to Yes, then all the data fields included in the
Detail section of a columnar report are kept together on
one page. This can be seen only when the report is
viewed in Print Preview or printed.
Detail Force New Page Set to None by default. The other settings are Before
Section, After Section, and Before & After. With all the
settings the Report Header appears only on the 1st page
and Report Footer only on the last page, while the Page
Header appears on top of every page and Page Footer at
the bottom of every page. The centre of every page is

53
 filled by groups. Group Headers appear only before the
1st record of each group, and Group Footers after the last
record of each group. When Keep Together property is
set to No and Force New Page property is set to None,
then all groups (consisting of Group Headers, group
records, and Group Footers) appear sequentially, and
completely fill each page. However, the other settings of
the Force New Page property, respectively enforce a
page break before, after, and before and after every
rendition of the Detail section (where each rendition
displays a different single record), so effectively every
record appears separately on a different page, while
Group Headers and Group Footers may, or may NOT,
appear on the same pages as the 1st and the last record
of every group respectively, depending on the setting of
the property Keep Together and the actual setting of the
property Force New Page.
Group Header Force New Page Set to None by default. The other settings are the same
as above. The Report Header and Footer and the Page
Headers and Footers are displayed as above. When
Keep Together property is set to No and Force New Page
is set to None, then groups are displayed in the same
way as described above for the same settings. The other
settings of the Force New Page property respectively
enforce the page break before, after, and before and
after every Group Header, so every Group Header
appears respectively on top of a new page and is
followed by some records of its own group, or at the
bottom of a page, after some records and the Group
Footer of the previous group, or on top of a separate
empty page.

Apart from those settings, there are also similar settings available in the Group, Sort, and Total
pane, which allow to keep whole group together on one page (that includes the Group Header, all
the group records, and the Group Footer), or keep header and one record together on one page,
what strictly speaking implies the header and at least one record.

Printouts for the level 2 assignments

To create a printout of any page of a form, with the Form Header and some text at the bottom of the
printout proceed as follows:

• Open the form in Design View and select Detail.

• In the Format tab of the Property Sheet set the property Keep Together to Yes
• Then set the property Force New Page to After Section
• Right-click on the form and select Page Header/Footer; two extra sections will be added to
the form
• Copy the Report Header title to the Page Header and reduce the Report Header height to 0
• Write the required text in the Page Footer

54
• Go to File> Print > Print Preview; you may select the page to be printed with the record
selectors, but it is NOT essential
• Click on the Print button. A Print dialog appears. By default, printing to pdf is selected. Set
the scope of printing to the desired page, e.g. from 57 to 57, and click the OK button.
• A dialog Save Printout As appears, which allows you to select the document title and
location.
• Once you entered that data, click on the Save button to save the document.

55