Reporting more than one set of sub-records

[Chrism2]

Hello folks; hoping someonw can point me in the right direction with reports here; I'm not sure what to "search" for - so hopefully this means something.

I have a simple enough table with Customer records in it. (tblCustomers). This has the usual things like address details. Its pk is customerID.

For each customerID, there may be multiple Contacts - listed in tblContacts - easy enough to create a form which displays the company and then a list of the contacts associated to that customer.

However, each customer may also have multiple activities - from tblActivities (basically marketing conversations).

What I'm looking to do is call the report with the CustomerID, show the Customer information, then all of the contacts (as one block) for that customer and then finally, underneath the contacts, list all of the activities by their date.

Am I looking for a sub-report? Or is there a better way?

Thanks!

 

[bohemian9485]

Create a report with tblContacts as underlying record source, save this report. Then create another report, this time using tblActivities as the record source. Save this too (we are going to use these two reports as the sub-reports in the main report). Now, create the main report with tblCustomers as record source. Insert the two reports saved before in the detail section by clicking the insert sub-report on the toolbar, in the properties window of the sub report, place [customerID] in both the Link Child Field and Link Parent Field properties (you can use wizard to create the sub reports to simplify things). Also put Yes in the can grow and can thrink properties of the sub reports so it wont make a gap in your report if the sub reports do not contain data.

 

[Chrism2]

Create a report with tblContacts.....

Thank you so much for the guidance; works perfectly.

I didn't realise the "can grow" function (or how it worked) - but I see now. A shame it doesn't work on forms!

Thanks again