Hi
I'm trying to produce a report, however, it is complicated by the fact that the information is coming from 4 different tables and even though they have the same key field in common, the line detail of each table have nothing in common with each other. When I bring the three line details together, via a query, into the detail section of the report I keep getting doubled up information because of the fact the line details from the three tables have nothing in common so a duplicated line is created for each line of the table that has the most values. That is: if table 1 has 1 value, table 2 has 3 values, table 3 has 6 values. So when you bring them all together the query creates 6 lines for because table 3 has 6 values. The 6 values in table 3 are valid but table 2 and 1 do not need six lines.
In theory what I need is a report that allows 3 different detail areas but as far as I can see that is not possible. I looked at sub reports but that does group all the like information to the key field value. it just puts another report at the bottom of the main report.
Any ideas??? Why is that when you design your database correctly it's usually a hell of a job trying to manipulate the data to do what you want. If I designed 1 table to incorporate all the fields in tables 1, 2 and 3, I'd be breaking all the design rules but producing this report would be a hell of a lot easier. However, I would then lose a lot of flexibility and functionality when it came to sorting, grouping and searching because I would then instead of having one field named Signatory, I would have a multiple number of fields to achieve the same result ie Signatory1, Signatory2, Signatory3, Signatory4, Signatory5, etc. Good grief!!!!
I'm trying to produce a report, however, it is complicated by the fact that the information is coming from 4 different tables and even though they have the same key field in common, the line detail of each table have nothing in common with each other. When I bring the three line details together, via a query, into the detail section of the report I keep getting doubled up information because of the fact the line details from the three tables have nothing in common so a duplicated line is created for each line of the table that has the most values. That is: if table 1 has 1 value, table 2 has 3 values, table 3 has 6 values. So when you bring them all together the query creates 6 lines for because table 3 has 6 values. The 6 values in table 3 are valid but table 2 and 1 do not need six lines.
In theory what I need is a report that allows 3 different detail areas but as far as I can see that is not possible. I looked at sub reports but that does group all the like information to the key field value. it just puts another report at the bottom of the main report.
Any ideas??? Why is that when you design your database correctly it's usually a hell of a job trying to manipulate the data to do what you want. If I designed 1 table to incorporate all the fields in tables 1, 2 and 3, I'd be breaking all the design rules but producing this report would be a hell of a lot easier. However, I would then lose a lot of flexibility and functionality when it came to sorting, grouping and searching because I would then instead of having one field named Signatory, I would have a multiple number of fields to achieve the same result ie Signatory1, Signatory2, Signatory3, Signatory4, Signatory5, etc. Good grief!!!!
