Show topics with multiple subtopics in a report (1 Viewer)

Hello all,

I looked this up quite a bit and couldn't seem to find any hints on how to proceed. So the situation is the following:

I have a table with some meeting topics, and I have another table with subtopics for each topic of which there can be multiple instances on no instances at all (let's say weekly updates if that makes sense).

What I would like to have on a report is all the information, all the topics, and for each topic, all the subtopics, if there are any. As an example:


I think you get the point.

What I tried so far is to make a query left joining the update table with the topics table and that seems to get all the data in one place, the only thing is that if more than one update is present for a topic, the topic will appear multiple times.

I then thought I could group the data into the report and I could ... sort of ... What I did is to add all the topic fields into the grouping header, that seemed to eliminate duplicates, but I don't like the results at all. First of all, the first (or only) update item does not appear on the same line as the topic, it appears below, and that would not even be that bad, I could live with that. The worst thing is that if a topic does not have any updates, empty fields are still present and they get shown even after grouping, as if being an actual entry. Even if updates are present, a blank line still appears along with the rest of the updates, even though it wasn't there before grouping. :banghead:

Can someone please elaborate this for me?
Thank you in advance!

Use a subreport for the subtopics

Interesting. Seems to work very close to what I want. Just one more issue, how do I make the subreport not visible when there are no entries in it for the current topic?

I could just make everything with white background, no alternating color rows, and no borders and just don't care if it's there, but I want to also be able to format a bit more without running to this problem.

Thank you very much.

As far as I know this is a play between CanGrow and CanShrink properties but I never understand very well this properties.

Another way to obtain what yo need is to design a query that include all the fields from the main table and from the sub-table. Use this query as RowSource for your raport.
Then, in the report, use Group option in order to group the information.

Can grow is enabled for all fields, can shrink is not. I will try to see how that works. But what I'm thinking that either way this will not work for a checkbox? I have one, it is actually the only thing visible since in current formatting everything is white. But the checkbox is still visible and it's annoying. I thought maybe there is a setting I am missing or a way to do it via VBA.

About the second proposal, with the query and grouping, I said in my original post, this was my first attempt and it's not good, an empty rows is present at every group (even though none are present in the query)

I am opened to other options as well.

Thank you again.

You seems to be well skilled so you need some ideas, not the code itself.

One more idea is to export data to Excel and using a simple macro to arrange it how you need in a worksheet.
In Excel you will be able to obtain the exact structure you show in your first post.

If the below picture meet your requirements, then you can do it with a sub report. I've attached a small database, open report "TopicTable" in "Print Preview" mode.

Thank you all for help, I managed to solve the issue, not exactly how I wanted but well enough for me. I thought it didn't work at first but I noticed it only shows in print preview mode.

What I did is to basically use subreports as Mihail originally suggested. The only issue was that empty subreports were still visible. I solved that issue by assigning .Report.HasData to the .Visible property in the "Format" event of the detail section. I also set the visibility to true on Load because after the print preview all subreports were rendered invisible.

Thank you all for help.

Ma bucur ca te-am putut ajuta. Bafta !

In English:
Glad to help you. Good luck !