Simple report getting complicated - same source twice in query

[Turncloud]

I have a simple database with a single table - called ECN



The records in this table are either Master-ECNs or Sub-ECNS, determined by a field called ECN_type.
If a field is a SUB-ECN then there is another entry in a field called related_ECN that records which Master-ECN the sub relates to.

This is all clear in a very simple report that shows the master-ECN number and a list of the sub-ECNS that relate to it (as below)



The source of the report is a query that pulls only these few fields from the main table and checks the 'type' field to only list sub-ECNS.

Now the customer wants me to show some additional fields about the Master-ECN (the number at the top) such as it's own Customer, Part Number fields, but I can't work out how to include this in my query (as a source). Any ideas on how to tackle this ?

Simon

[rpeare]

if you aren't using a report/subreport format you may want to consider this. Your report isn't complex and it may be a better option for you. in other words your 'main' report would be built on the 'master' records and your subreport would be built on the 'detail' records linked on the related_ECN to master_ECN.