"The power in learning the design environment for an Access Report will give you total control and that polished look and feel..."

Report canvas
Learning the components of an Access report in the design mode will serve you well for the more challenging reporting outputs that an Access wizard simply cannot provide.

Remember; Report wizards (and templates) provide the quick and simple steps to the basic Access report outputs and previews which the basic designs are really never good enough. In all cases, you will need to be in a report design view to enhance the look, feel and functionality further.

This section focuses on the design area starting from scratch so you can master the main properties and their controls and then appreciate where the wizard tool stops and you the developer take over.

Terminology
Lets start by introducing you to some terms for an Access report design canvas.

Report sections - There are essentially up to four main sections to an Access report which are called Detail, [Field] Group Header/Footer, Page Header/Footer, Report Header/Footer.

The Detail section is the more commonly used area which will hold the main details in a report including data/records in either a tabular or columnar layout or other repeatable information.

The [Field] Group Header/Footer section is bound to a field or expression and acts as the group of records that belong to it. You can have more than one group (one for each field or expression) in an Access report and gives it a natural way to output data into sub divisions. You can resize the height of either header or footer independently so that you may only want to view one of the two sections. Group calculations are normally applied here in either the header and footer section.

Note: You can only use this type of section when the Access report is bound to a data record source and where you bind it to a field of your choice.

The Page Header/Footer section is used to display information at the top and bottom of every new page shown for a report. You can resize the height of either header or footer independently so that you may only want to view one of the two sections.

The Report Header/Footer appears at the top and bottom of the whole report and is used to display headings, calculations and summary data. You can resize the height of either header or footer independently so that you may only want to view one of the two sections.

The following illustration below shows the order of all the sections if switched on.

To switch the view on and off for an area:

In Access 2007 (and later versions), from the ribbon bar in the Report's design view locate the Arrange tab and to the right (end) look for Show/Hide section. You will see two on/off buttons one for a Report and a Page.

In Access 2003 (or earlier), from the menu bar click on View and you will see both Report Header/Footer and Page Header/Footer options which is an on/off switch.

Note: Switching on and off group sections is covered later in this article.

Controls - These are the components you add to an Access report and include a variety of different types including the more popular Textboxes, Labels, Lines and shapes.

The more components added to a report, the richer and more professional the output will be. The container to all the controls can be found in the Controls section from the Design tab on a ribbon bar (for Access 2007 and later) and in a Toolbox loaded from a button on a Toolbar (for Access 2003 or earlier).

To add a control, click the required component and click in the report section roughly where this control is to be positioned.

For a Textbox, two components are included (Textbox and a Label). Handles appears around the component to allow you to move or resize the control into position by clicking and dragging with the mouse.

Other controls are added in the same way.

Properties / Property Sheet - There are many properties in an Access report due to the fact you have properties for every component added.

Properties are the attributes that can be changed to a report or to a control so it can look, feel and even behave differently.

It would be too extensive to list all properties for every control and my recommendation will be to spend a little time browsing and using the help files to establish the benefits for an attribute.

Tip: Click on a property of interest and press the F1 function key from the keyboard to load the context sensitive help document.

In Access 2003 (or earlier), properties can be found via an icon on Toolbar called Properties which displays in a pop-up window.

In Access 2007, you have what is known as a Property Sheet which can be found from the ribbon bar, Arrange tab under Tools section.

In all cases, you will find tabs across the top which divide many properties into natural categories and is sensitive to which control or report section you are currently focused on.

The Format tab for the selected component or section of a report allows you to modify the look and feel which is an extension from the main ribbon bar or toolbar formatting features.

It also includes how the report will be displayed, any window controls present, how the window view is displayed, which print page setup options you want.

Each control will have its unique collection of formats too.

The Data tab for a report or control (where applicable) will provide the bound link to the recordset (table or query) or the field in a table or query. Various output formats and data input masks and running sum are available for a Textbox with the option to enable the control.

For the entire Access report design, this tab can be set to allow filtering and sorting. Therefore, a report can be set to show only the required records in a certain order.

This tab will appear mainly blank for a control not relevant for data use namely a Label which only has a smart tag.

The Event tab is where users can add code (macro, module or an expression) to be triggered when that event takes place.

One common event example for a report may be when you run (load) the report (On Load event) to run a procedure to display a custom built toolbar and another to hide it when the report closes (On Close event).

Not all events are applicable; for instance a Line control has no event associated and therefore cannot be triggered.

Note: You will need to know more about macros and modules before utilising this feature.

The Other tab is for all other properties that are miscellaneous to the selected control.

These include if a report has a custom built menu, toolbar or ribbon bar attached to it, if it is pop-up and/or a modal screen and whether it has a module attached to it.

Once again, each control will vary the property options but all controls can be named here for ease of use.

Basic Access Report - design example
The following steps will show you which areas and properties are manually set for a basic customer and orders list report.

Note: Arguably, using the Report wizard control method maybe a quicker way to achieve this but you can always combine methods to get the best of both as not all the properties are set with a wizard or template.

Most reports of this type will normally be built either on a query or an SQL Structured Query Language statement embedded into the report as you will probably want to combine several tables joined in a relationship, carry out calculations in a query and apply some criteria in a query.

I want to produce a list of all 'UK' customers who placed an order in the first quarter of 2009 that were despatched within 30 days of being processed.

The layout, formatting and other attributes will be changed to help group records by each month (January, February and March) with group and report totals in the correct sections.

The Access report should look something similar to

I'm using Access 2007...

Step 1: We need to create the query to support the report. Design a new query and set the fields, criteria and any other attributes required as the source file for your report.

If you prefer, you can write and use SQL and attach it to the Record Source property of the report to bind the source internally.

I'm going to create a separate query object with the following SQL generated for me:

Note: You can copy the above SQL into a new query or directly into the report to save time. You will need to download the database file for your version.

The query will look something similar to

Test the query and make sure there are no errors and that all the criteria applied is logically correct.

Save the new query. I'm going to call it qry_CustomerOrders_Qtr1_2009_UK_OnTime.

Step 2: Start a blank new report design by going to the ribbon bar, select the Create tab, in the Reports section and click on the Report Design icon.

Step 3: You should now be in the report design view which shows a standard blank canvas.

Make sure you also show the Property Sheet window by clicking the Property Sheet icon from the Design tab, Tools section on the ribbon bar.

In my example, I also want to make sure the Page Header/Footer areas are visible (which normally defaults this way) in addition to the Details area. If not, click the Page Header/Footer icon from the Arrange tab on the ribbon bar. Also include the Report Header/Footer sections by clicking the respective icon from the same section.

Note: We will also need to add a group section too for the order date which is to be grouped by month but this will be added later on.

The Access report should look similar to:

Step 4: In the Property Sheet, you need to connect this report to a Record Source which is located from the Data tab for the report.

From the drop-down list in the Record Source property, choose the table or query available. I've chosen qry_CustomerOrders_Qtr1_2009_UK_OnTime as in step 1.

This now binds the source to the report design and establishes a connection to the fields in the selected query.

Note: A table or query object is the normal connection for the Record Source property. You can however write SQL directly into this control too which belongs only to the report.

Step 5: Once a data source has been established, you will be able to add bound fields (labels and textboxes) to your report.

You will need to display the Field List window by locating Add Existing Fields icon from the ribbon bar, Design tab, Tools section.

Note: In Access 2003, the Field List window is displayed via the toolbar in Design view mode for an Access report.

Notice only the fields listed are from the query source only which may well be built from multiple related tables (as in my example above using two tables; customers and orders).

Select the field(s) you wish to add to your report by dragging and dropping into position. Be careful to leave enough room so that both the label and textbox can be easily seen otherwise some resizing will need to be carried out.

Add the following 6 fields to the detail section:

Order Date
Company Name
Order ID
Shipped Date
Order Amount
Freight

Leave the Country field out of the Access report as this was used to filter for orders in the 'UK' within the actual query.

You will notice a Label and Textbox control are added together for each field. You will need to cut and paste all labels separating them from their respective textboxes and position them in the Page Header section.

Note: You can select multiple labels using the CTRL key from the keyboard and then cut (CTRL + X) before pasting them (CTRL + V) into the page header.

You will need to do some aligning and resizing for each label and matching textbox. The best approach is to select both controls together (using the CTRL key again) and right mouse click and make your selection.

Reduce the Detail and Page Header section heights so that the records will appear tight in a list.

Test your report to see the records in print preview or layout view mode.

Save your changes (give a meaningful name).

Step 6: Next, I'm going to add a Group section as it is required for this Access report because I want to be able to group my records by month (first three months of 2009).

I can use the Order Date field because it's a Date/Time data type field and will recognise the month interval in a date. See understanding Access data types for more information.

To add a group, from the ribbon bar for the design view report choose the Design tab, under the Grouping & Totals section where you will see the Group & Sort icon.

A window pane appears (normally at the bottom of the screen) which is blank with two large buttons in the middle. Click Add a group button which pops up a field list in this view where you can pick Order Date.

Along this banner, you can choose to add different intervals (for the date), set a title, add totals to both the group and report and show or hide one or both header and footer.

Expand the arrows in each part and set accordingly.

Step 7: Now I want to add a Textbox control for the Detail section to show the monthly heading per group without the need to repeat this item for each record and calculate how to show only the month and not the date.

Locate the Design tab, Controls section from the ribbon bar of the Access report and click Text Box icon.

Move you mouse into the Detail where you wish to add the new control. Cut and paste its associated Label into the Page Header section (as described earlier).

Now we need to add a calculation for this new control. In the Property Sheet for the selected Text Box, locate the Control Source property from the Data tab and click the button with three dots on it (do not choose the drop-down arrow for this property) to open the Expression Builder tool.

We need to use this tool to write and/or pick functions and other expressions to show the following:

Use the Expression Builder tool to know more about functions and calculations which is covered in more detail within queries but is the same tool for all objects.

The Format function uses the narrative mmmm to extract the month from a date.

To prevent the duplication (as shown above), we need to modify another property for this control by locating Hide Duplicates from the Format tab and set this value to 'Yes'.

Save changes and test it again.

Final Step: There are several other settings for formatting and adding other controls as desired for your Access report including inserting page numbers, dates and border & shading for controls and sections.

The only last attribute I want to show you is how you can get each row to alternate between colours (as shown above).

There is an icon located in Design tab for the Access report under the Font section and is called Alternate Fill/Back Color icon.

Make sure you first select the Detail section in your report and then choose this icon and apply you choice of colour.

Save changes, run report and job done!

Keep experimenting with various other properties and formats so you know how to control the output of your Access report.

There are too many attributes to show you here but hopefully you can now go off and investigate properties at your pace.

Remember, you can always call the help system for further information - just click in the property first and then press the F1 function key!

Return from Access Report to Access Reports
Return to About Access Databases home page