Report grouping from Query source with Ambiguous Outer Joins

Hi,

You are confused by many-to-many relationships. That is quite normal.

If you look at your form, you have meetings, linked to sessions, in turn lined both to attendees and to notes. Each link is represented by the link fields of a subform. So you have two subforms on the subform 'sessions'.

    meetings  1-n  sessions  1-n  attendees
                                            1-n  notes

If you want to create a report with a single query, you can choose only one path, either attendees or notes. If you want both, imagine the problem: you have three people and five notes. You ask Jet to combine these in a single table. Jet will be happy to comply, giving one copy of each note to each person. In other words you get 15 rows, showing all possible person-note pairs.

Technically, this is called a "Cartesian product"; everybody gets bitten the first time, but it's in fact both logical and useful in many situations (not this one).

If you add a relationship to your table structure, for example that each note is signed by one of the attendees, you could use this:

    meetings  1-n  sessions  1-n  attendees  1-n  notes

That can be displayed with a report having a single query: the notes will be placed under each attendee having signed them. But this isn't the layout you want.

Start for example with this query:

    meetings  1-n  sessions  1-n  attendees

Once you are happy with the result, and are ready to add the notes below the list of attendees, create a subreport. It should be placed in the footer of the 'sessions' group, below the 'attendees' details, linked (just like the forms) through Session.SessionID to Notes.SessionID.

You can also do the opposite, start with

    meetings  1-n  sessions  1-n  notes

And add a subreport to show attendees in the group header of the 'sessions' group, above the 'notes' details.

Does that make sense?
(°v°)

Afterthought:

Your structure is ideal to show one useful aspect of the Cartesian product. If you were to add a button on your subform 'session' to print the notes, on on each page, you could do exactly that: add both the related notes and the related attendees to your query. This would print out 15 pages (the five notes for the three attendees), exactly the right number.

Your current reports try to do just that: print the 15 needed copies of the notes.

(°v°)

@Helen_Feddema

Apologies for my Report naming, as they were based on my Queries I forgot to rename them with the rpt prefix.

I did try using the Meeting ID as the top grouping but it still didn't work for me, am I going about joining via Queries? If I do not use linked Queries then I get the 'ambiguous outer join error'.

QryTest3 incorporates QryTest2 and QryTest3. Thanks for the good advice. I think harfang nailed it when he said about the "Cartesian product".

@boag2000

Thanks for you help and advice. There must be something wrong with my Query. When I run the Report Wizard I don't get the option: 'How do you want to view your data?'. Instead the wizard goes straight to the 'Do you want to add any grouping levels?'. If I add MeetingID then I don't get any of the other fields pulled through and grouped with it and it ends up as an orphan.

@harfang

Thanks for your help. I am really having trouble getting my head around how to prepare the source for the Report. The relationships in the DB is set in a way that causes errors when trying to write a Report.

To be honest I am all but ready to give up the ghost as I cannot seem to find a way around the querky tables setup and I thought that linking through Queries would have resolved that but the "Cartesian product" you mentioned is obviously causing a problem and I don't know how to get around this, I guess I have to go back to the drawing board and try research "Cartesian product" on Google and see what comes back.

Even after all the advice given here I am none the wiser, I guess I need get past the whole "Cartesian product".

Thanks for the detailed feedback. I with more Askers would take the time to do the same.

Don't get too hang up on the whole Cartesian product issue. Just accept is as the reason why you can't use a single report to show both the attendees and the notes of a session. Bottom line: you can't prepare a single source for everything, no matter how you massage the data. Either attendees or notes (or both) must be displayed as a sub-report. It's not hard to do, it's just like sub-forms.

Cheers!
(°v°)

Thanks for  your advice harfang. So I need to research sub-reports? I shall look into this. I must admit it is very difficult being a noob and trying to learn all the features of Access. I guess I need to learn to walk before I run.

Thank you for your guidance it was most useful. I shall have another crack at the Report.

I did try editing the relationships in the Query but this didn't work either.

I found some really useful information in the link below regarding Cartesian product. I shall study it further.

http://www.databasedev.co.uk/multiple_table_queries.html

<I don't get the option: 'How do you want to view your data?'. Instead the wizard goes straight to the 'Do you want to add any grouping levels?'. If I add MeetingID then I don't get any of the other fields pulled through and grouped with it and it ends up as an orphan.>

...I'm not sure what this means, or what happened..

<I don't get any of the other fields pulled through and grouped with it and it ends up as an orphan>

...Are you saying that this is what the report looked like when you finished the wizard a viewed it?

Can you post a graphical example of what you want the report to look like based on the sample data?

Jeff

Your query looked OK, so I agree with boag, a picture of what you want in the report would be very helpful.  I am sure it can be done.

Hi Helen and Jeff, thanks again for your advice and help.

I have uploaded another sample and I have made the changes that Helen suggested I have renamed the reports so that they have the correct rpt prefix and not the qry. I have also created an extra meeting which also has two sessions which may help with Report grouping.

What I have done is make a mock up Report which is basically many labels and I manually typed in the text. I created page breaks to demonstrate how I envision how the report should look if possible.

The test report that I created is the one called 'rptReportMockUp4EE' I hope it makes sense.

Thanks again for your help.

And just for clarity, ...what Table/Query is to be the source for this report?

I did use Query: 'qryForPrintMinutes' but the latest query I created was: 'qryTest3' and I had hoped that this would be the best.

The report: 'rptTest4' was the nearest I could get to what I required but it is not quite right.

I hope this helps.

Thanks.

Did you try importing the reports from my previous post? -- (°v°)

@harfang

This is fantastic! Thank you very much This is exactly the layout I wanted.

What did you use as the source for both reports? was it my 'qryTest3'?

I have also found a few sites that explain subreports and their similarity to subforms.

Thanks again for your help.

Another question, on my subform sometimes the navigation buttons disappear or when they do appear if if I click the next record to show the next session the top line of my subform disappears. The line that has the session start, end and room location. No matter how I try to resize the subform I cannot seem to correct this issue. What am I doing wrong?

Ignore my previous comment about the subform I seem to have resolved it.

> What did you use as the source for both reports?

The queries are embedded in the reports, see the record source property. The main report links meeting to sessions and notes, the subreport links attendees to people.

I'm glad you liked it, and believe me there is way to produce this with a single query / report.

Cheers!
(°v°)

Thanks again for the additional information. I checked the record source property and although I don't fully understand how that works in certainly looks interesting.

>'m glad you liked it, and believe me there is way to produce this with a single query / report.

Would this be from one of the queries that I already have in the database or would  you need to make a new one?

I am intrigued and would love to know how but my skills are very limited. I am very grateful for your help I am learning all the time.

Many thanks.

Michael

My lazy fingers! please read "there is NO way to..." I was trying to reassure you once more that you will need at least one subreport to avoid the "Cartesian product" syndrome. Your query attempts weren't deficient in any way, but the task was impossible.

(°v°)

Oh I see. Thank you very much for clearing that up for me, that vital piece of information will help me greatly in the future. At least I now know it cannot be done in one step. Also learning about the "Cartesian product" syndrome has opened my eyes also so than you so much for that piece of info.

Thank you all for your help, I really appreciate it. Now I need to go off and practice some more with Reports and Queries.

Kind regards
Michael

I have been priviledged to receive expert information from the 3 experts in my thread. I have learned a lot of new information which will arm me in the future with regards to relationships, queries and reports with subreports. I am very grateful to all. Thank you.

You are very welcome.

Re-reading, I notice that the question's title, ambiguous joins, has not been properly addressed. Let's take a three table query like this:

    Sessions  1--n  Attendees  n--1  People

This is a typical many-to-many relationship. There can be many attendees per session (1--n) and many attended sessions per person (1--n), so there can be many sessions per person and many people per session (n--n). The table attendees creates this many-to-many relationship between both "parents".

For comparison, this is a "Cartesian product" query, by reversing the direction of the one-to-many (many-to-one?) relationships:

    Attendees  n--1  Sessions  1--n  Notes

This is a distribution list: each attendee get a copy of every sessions' notes. The "parent" table is in the middle.

Going back to the first query, if you want to see all sessions, even those for which not attendees have been entered yet, you need a LEFT JOIN, often shown as an arrow:

    Sessions  --->  Attendees  --->  People

You could reverse it to see all People, even those not scheduled to attend any meeting. What you can't do is see all sessions and all people:

    Sessions  --->  Attendees  <---  People

That's an "ambiguous join". The Jet Engine needs to start exploring from one "root" table, and follow the arrows from that root. If there are two or more "roots", it finds the query "ambiguous", and tells you so.

The following is legal, and it's equivalent to the default INNER JOIN query, because there are no unmatched records in the table Attendees (they always have two parent records).

    Sessions  <---  Attendees  --->  People

The query in the report is basically

    Meetings ---> Sessions ---> Notes
    [subreport:]                 \---> Attendees --- People

(All meetings, even those without a planned session; all sessions, even those without notes; plus an attendance list if available) You could also use:

    (Meetings --- Sessions) ---> Notes

(All sessions and related notes, but no meetings without a scheduled session.) This wouldn't be ambiguous either.


Thanks and success with your project!
(°v°)

;-)