D
Deleted member 73419
Guest
Hi,
I have a complicated question which I'm trying to describe simply, so will initially start with the data.
The data I need to represent on the report is contained within three tables. I have managed to cobble together a query which displays the data but there is a lot of duplication which I want to remove for a report so that it is simpler to read and understand.
The three tables are OVR, BND and FNT. For each record in OVR.AAA, I need to find which values lies between BND.B and BND.C
I then need to join BND.ID to FNT.id to list the records which correspond to the values from BND.
This is briefly summarized here:
Query1 is constructed by:
Now the complicated part, the report.
Taking just one entry from the query, I've highlighted the areas where the duplication occur which I want to remove:
I want each record to contain the contents of the yellow rectangle. I don't need the duplicate entries from columns AAA, AA, BB, A, B, C & D - I only need each text to appear once.
The actual layout isn't too important in that I have missed the column title labels off but I'm trying to simplify the question.
What is the best way to achieve this? Would nested queries, each filtering out the duplication and then link a report? Or would nested reports be the way to go?
I've attached the database as it contains more data for context.
Many thanks
I have a complicated question which I'm trying to describe simply, so will initially start with the data.
The data I need to represent on the report is contained within three tables. I have managed to cobble together a query which displays the data but there is a lot of duplication which I want to remove for a report so that it is simpler to read and understand.
The three tables are OVR, BND and FNT. For each record in OVR.AAA, I need to find which values lies between BND.B and BND.C
I then need to join BND.ID to FNT.id to list the records which correspond to the values from BND.
This is briefly summarized here:
Query1 is constructed by:
SQL:
SELECT ovr.AAA, FNT.AA, FNT.BB, BND.A, BND.B, BND.C, BND.D
FROM (BND INNER JOIN FNT ON BND.id = FNT.id) INNER JOIN OVR ON (ovr.AAA <= BND.C) AND (ovr.AAA >= BND.B);
Now the complicated part, the report.
Taking just one entry from the query, I've highlighted the areas where the duplication occur which I want to remove:
I want each record to contain the contents of the yellow rectangle. I don't need the duplicate entries from columns AAA, AA, BB, A, B, C & D - I only need each text to appear once.
The actual layout isn't too important in that I have missed the column title labels off but I'm trying to simplify the question.
What is the best way to achieve this? Would nested queries, each filtering out the duplication and then link a report? Or would nested reports be the way to go?
I've attached the database as it contains more data for context.
Many thanks