Understanding Databases – Present Specific Fields

Present specific fields in a grouped report

15 Present Specific Fields

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.

16 Relationship

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

17 New Relationship

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

18 Make Relationship

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.

19 Joining Line

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

20 Create Query

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

21 Add Fields

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.

22 Report Wizard

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.

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

24 Choose Layout

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

Click ‘Finish’ to display the report.

25 Display

The report document may look badly formatted.

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

26 Format Report

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.

27 Grouping and Sorting

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.