Present Specific Fields

Present specific fields in a grouped report

For this section, create a new table to display salary and another to display bonuses.

This data will require a grouped report to display correctly as there are multiple entries for employee-ID.

Next, we need to create a relationship, in the main window, click ‘Relationships’ from the ‘Tools’ menu.

Click ‘Add Tables’ from the ‘Insert’ menu. This will give you the option to select a table and add the newly created tables.

To make the relationship, click and hold on a field in one table, then drag across to the one you want to create a relationship with in another table.

Lines symbolising the links will join the two fields.

Right click the joining line and click edit.

Select ‘Update cascade’ and ‘Delete cascade’ from the Relations menu.

Click OK, then save the relationship and close the window

Next create a query in design view.

Add the tables you want to use in the query (in this case Employee Basic Details and Salary and Bonus).

Add the desired fields from the three tables.

Select sort ascending for last name to get results in alphabetical order.

Click the save icon and name the query.

From the main window, select ‘Use Wizard to create report’.

Select the Query you made earlier.

Add the fields that you want to display in the report.

Click ‘Next’ until you get to step 3-Grouping.

Set up groups for data that you don’t want to be repeated in the report.

For this example, don’t add Bonus and Bonus Date as some people received more than one bonus.

This will display the Grouped fields, followed by each bonus earned, rather than duplicating all of the data.

Click ‘Next’ until 5-Choose layout.

Choose the layout you prefer. For this example, Columnar, two columns is best.

Click ‘Finish’ to display the report.

The report document may look badly formatted.

Notice how fields don’t line up and grouped data splits across pages.

In the main window, right click the report and select Edit.

To align correctly, click each text box individually, then click the ‘align left’ icon from the ‘align’ toolbar.

This will line up all the text boxes to the left.

Click on the horizontal line dividers and press delete. Add one horizontal line above ‘Last Name’ group to divide records.

Click the ‘Sorting and Grouping’ icon from the toolbar.

To stop groups splitting over pages, click each group separately and select ‘Keep Together’ – ‘Whole Group’ from the drop-down menu.

To check the formatting click ‘Execute Report’ from the toolbar to open the report document.