How to avoid repeated fields in reports

[tyemm]

I have wrestled with this for sometime: My report is displaying more of a matrix, and less of a summary, than I want.
Briefly, I have a main table, called Complaints. Its ComplaintID Primary Key is a foreign key in several other tables, for example, in Departments, as well as in Functions. Using outer joins, then, if I have one Complaint, involving (i.e. with the ComplaintID as a Foreign Key) 2 different Departments, and 4 different Functions (also with the ComplaintID as a Foreign Key), my query displays this as 8 lines (that's why I'm calling it a matrix, i.e. 1 x 2 x 4).
I don't see a problem with that (I've got outer joins on each of two successive, i.e. nested queries). But I cannot get my report to give me a simple listing, without repetitions.
What I want is something like:

Complaint No.1:
Department A
Department B
Function I
Function II
Function III
Function IV

Instead, I'm getting:

Complaint No. 1:
Department A
Function I
Function II
Function III
Function IV
Department B
Function I
Function II
Function III
Function IV

Actually, I've got more nested queries, so it's really worse than this, but this is the paradigm I've got to master. What is the trick to controlling reports?
Tnx, Tom

 

[dhookom]

You can't place all tables into a single query. You can use subreports for some of the related tables. I expect you are using subforms for data entry. Use the same setup for your report structures.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tyemm]

Thank you dhookom!

I was beginning to think it was time to learn about subreports -- I'm heading to relevant threads today!
Tom

 

[tyemm]

I've made great headway today with subreports, links, etc.
Two issues have arisen for me; the first is formatting the appearance of subreports alongside other fields in the main report--I typically am getting large gaps, meaning that my subreport begins displaying only below the last line of a main report fields (one that has "Can Grow/Shrink" enabled, by the way). I can get it somewhat better by cheating individual fields/subreport controls up and down, but what is the general concept here?
Second is the more basic question: how do I get the subform, and its header, not to display when there is no associated data? I've seen on many posts the piece of code
Me.subreport.visible = Me.subreport.hasdata, but it's not clear to me exactly where this must be placed, and if it is really: Me![my subreport name].visible, etc.
Thanks to all on this wonderful site!
Tom