How do I create a report with multiple 1-M relationships?

I need to create a report that shows, for each master row, two sets of child (detail) rows. In database terms you can think of this as table 'A' having a 1:M relationship to table 'B' and also a 1:M relationship to table 'C'. So, for each row from table 'A', I want to display a list of child rows from 'B' (preferably in one section) and a list of child rows from 'C' (preferably in another se开发者_高级运维ction). I would also prefer to avoid the use of sub-reports, if possible. How can I best accomplish this?

Thanks.

---

I think I understand your question correctly, ie for a given row in Table A, you want the details section(s) to show all connected rows in Table B and then all connected rows in Table C (for any number of rows in B or C, including zero). I only know of two solutions to this, neither of which is straightforward.

The first is, as you've guessed, the disliked subreport option. The second involves some additional work in the database; specifically, creating a view that combines the entries in Table B and Table C into one table, which can then be used in the main report as a linkable object to report on and you can group on Table A as desired. How straightforward this is will depend on how similar the structures of B and C are. If they were effectively identical, the view could contain something simple like

SELECT 'B' AS DetailType, Field1, Field2, FieldLinkedToTableA
FROM TableB
UNION ALL
SELECT 'C' AS DetailType, Field1, Field2, FieldLinkedToTableA
FROM TAbleC 

However, neither option will scale well to reports with lots of results (unless your server supports indexing the view).

---

This is exactly what Crystal was made for :)

1. Make a blank .rpt and connect to your data sources as you normally would.
2. Go to Report->Group Expert and choose your grouping field (aka Index field).
3. You will now see a Group Header section in your design view. This is your "Master row". The Details sections will be your "Child rows".

In the example image below, this file is grouped by {Client}. For client "ZZZZ", there are 2 records, so there are 2 details sections while all the other clients only have 1 details section.

Edit

Based on your response, how about this:

1. In your datasource (or perhaps using some kind of intermediary like MS Access), start SQLing as follows.
2. Make a subquery left joining the primary key of TblA and the foreign key of TblB. Add a third column containing a constant, e.g. "TblB"
3. Make a subquery left joining the primary key of TblA and the foreign key of TblC. Add a third column containing a different constant, e.g. "TblC"
4. Union those 2 queries together. That'll be your "index table" of your crystal report.
5. In Crystal, you can have multiple grouping levels. So group first by your constant column, then by the TblA primary key, then by the foreign key.

This way, all the results from TblB will be displayed first, then TblC. And with a little work, tables B & C won't even have to have the same field definitions.

---

You can use or create columns that are used for grouping, then group on the table A column, then the table B column, then C. (Crystal's group is not the same as t-sql "group by". In Crystal, it's more of a sorting than a grouping.)