I have four groups. Each group is associated with a different table.

Group 1, Table 1: Fleet ID - a unique number that is never duplicated. All other tables are linked to this table by Fleet ID.

Group 2, Table 2: PO Number - There may be multiple POs associated with each Fleet ID.

Group 3, Table 3: Service Order - There may be multiple Service Orders associated with each Fleet ID.

Group 4, Table 4: Sublet Reference - There may be multiple Sublet References associated with each Fleet ID.

Currently, the report is duplicating Service Orders twice (because there are two POs), and duplicating Sublet References three times...twice...because there are three Service Orders and two POs.

It looks like this...


and I'd like it to look like this...


Can you help?

Edited by tpreston - 20 Jul 2023 at 11:19am

Because all of the data is joined to the fleet ID and there is no apparent relationship between PO, Service Order, and Sublet other than the Fleet ID, you'll need to use subreports for this. Here's how I would set up the data:

1. Main report - Group by fleet ID and show the PO data in the group header.

2. Add a fleet ID group header section below the one where the PO data is located. In that section add a subreport that shows the Service Orders and use the fleet ID as a subreport link to get the service orders for the specified fleet ID.

3. Add a third group header section and put a subreport that shows the Sublet Reference data, using the fleet ID as a subreport link to show just the sublets for the current fleet ID.

-Dell