Master-Detail-Subdetail problem (many-to-one)
I have a problem with a Master-Detail-Subdetail report.
The data structure in my Delphi application is :
MASTER : TADOQuery
Year~Num : key field
DETAIL : TADOTable
Year~Num : composite key field
SpID : composite key field
MasterSource : MASTER
SUBDETAIL : TADOTable
SpID : key field
MasterSource : DETAIL
The structure is therefore : MASTER --(one-to-many)--> DETAIL <--(many-to-one)-- SUBDETAIL
MASTER has 21 records :
the first 17 records have NO matching DETAIL record
the last 4 records do have matching DETAIL records
DETAIL has 10 records
5 records point to MASTER(21)
3 records point to MASTER(20)
1 record points to MASTER(19)
1 record points to MASTER(18)
SUBDETAIL has 17 records
only 8 records are referenced in the DETAIL table
The problem is that when I cycle through the MASTER dataset the first record always picks up the last DETAIL record, when it should not be picking up anything. Records 2..17 pick up Nothing and records 18..21 pick up their correct DETAIL and SUBDETAIL records. {Output summary attached}
If I delete the curent record 1 from MASTER, the problem persists, with the NEW record 1 still acquiring the wrong DETAIL data.
In my FastReport this translates into MASTER record 1 displaying no DETAIL record but ALL 8 of the SUBDETAIL records (when there should be none). MASTER records 2..21 display correctly.
The dataset also displays incorrectly in my Delphi application.
Can anyone please tell me how to correct this fault ?
I have tried to hide the unwanted SUBDETAIL output in the FastReport, but haven't found a way yet.
The data structure in my Delphi application is :
MASTER : TADOQuery
Year~Num : key field
DETAIL : TADOTable
Year~Num : composite key field
SpID : composite key field
MasterSource : MASTER
SUBDETAIL : TADOTable
SpID : key field
MasterSource : DETAIL
The structure is therefore : MASTER --(one-to-many)--> DETAIL <--(many-to-one)-- SUBDETAIL
MASTER has 21 records :
the first 17 records have NO matching DETAIL record
the last 4 records do have matching DETAIL records
DETAIL has 10 records
5 records point to MASTER(21)
3 records point to MASTER(20)
1 record points to MASTER(19)
1 record points to MASTER(18)
SUBDETAIL has 17 records
only 8 records are referenced in the DETAIL table
The problem is that when I cycle through the MASTER dataset the first record always picks up the last DETAIL record, when it should not be picking up anything. Records 2..17 pick up Nothing and records 18..21 pick up their correct DETAIL and SUBDETAIL records. {Output summary attached}
If I delete the curent record 1 from MASTER, the problem persists, with the NEW record 1 still acquiring the wrong DETAIL data.
In my FastReport this translates into MASTER record 1 displaying no DETAIL record but ALL 8 of the SUBDETAIL records (when there should be none). MASTER records 2..21 display correctly.
The dataset also displays incorrectly in my Delphi application.
Can anyone please tell me how to correct this fault ?
I have tried to hide the unwanted SUBDETAIL output in the FastReport, but haven't found a way yet.