list all records in query based on relationship

[Jen0dorf]

Hi

I have a database with three tables, tbl_deceased.tbl_join and tbl_Memorial. Not every deceased person has a memorial. The relationship is many to many between memorials and deceased hence the join table



tbl_Deceased contains 39741 records

tbl_join contains 39741 records



both are linked by graveId and fkgraveID


tbl_Memorials contains 17699 records

and is linked to tbl join by MemorialID and fkMemorialID


If I create a query using all three tables

I only see 25482 records

If I delete tbl_memorial from the query and run it I see 39741 records.

Is this correct or have I missed a tick box?



thanks

Ian

[pbaldy]

If your question is how to see the "missing" records in the query, double click on the join line to that table in the query to edit it. The correct choice should be evident.

[JoeM]

The default join is an "INNER JOIN", which only returns records that have matches.
If you want to return records for ALL deceased members, regardless of whether or not they have had any Memorials, you will need to do a LEFT JOIN between your tblDeceased and tblJoin tables.

If you double-click on the Join line shown above between the two tables, it will allow you to do this easily. Just select the appopriate option (the one that begins someone like "Show all records from the tblDeceased table...".

[Jen0dorf]

Hi Guys

As usual so simple when you know!!!

thanks

Ian

[pbaldy]

Happy to help!

[JoeM]

You're welcome!

[Jen0dorf]

Hi

deep and abject apologies if I ramble on but I am as usual confused.

Have a query based on a three tables (tbl deceased, tbljoin and tbl memorial) that when run does not show all records. I realised that Not all records in tbl memorials have matching entries in tbl join.

The relationship is



Courtesy of the forum I found right clicking on the join of a query gave me three options.

My original query is below and is I think an inner join only showing matching records

SELECT tblDeceased.GraveID, tblDeceased.Mlink, tblDeceased.Plot, tblDeceased.GraveNo, tblDeceased.Forenames, tblDeceased.Surname, tblDeceased.Age, tblDeceased.DayOfDeath, tblDeceased.MonthOfDeath, tblDeceased.YearOfDeath, tblDeceased.DayOfBurial, tblDeceased.MonthOfBurial, tblDeceased.YearOfBurial, tblDeceased.Inscription AS tblDeceased_Inscription, tblDeceased.pp, tblDeceased.Notes, tblDeceased.Links, tblDeceased.map, tblDeceased.notes_2, [tblDeceased]![Notes] & " " & [tblDeceased]![notes_2] AS [Complete Notes], tblDeceased.pdfMap, tblDeceased.GraveNotes, CDate([DayOfBurial] & "/" & [MonthOfBurial] & "/" & [YearOfBurial]) AS FullDateofBurial, CDate([DayOfDeath] & "/" & [MonthOfDeath] & "/" & [YearOfDeath]) AS FullDateofDeath, tblMemorial.Mlink, tblMemorial.Inscription
FROM tblMemorial INNER JOIN (tblDeceased INNER JOIN tblJoin ON tblDeceased.GraveID = tblJoin.fkGraveID) ON tblMemorial.MemorialID = tblJoin.fkMemorialId
ORDER BY tblDeceased.Forenames;

So I selected option two in the join between tbljoin and tbl memorial so that "All records from tbl memorial are included and only those from tbljoin where the joined fields are equal

SELECT tblDeceased.GraveID, tblDeceased.Mlink, tblDeceased.Plot, tblDeceased.GraveNo, tblDeceased.Forenames, tblDeceased.Surname, tblDeceased.Age, tblDeceased.DayOfDeath, tblDeceased.MonthOfDeath, tblDeceased.YearOfDeath, tblDeceased.DayOfBurial, tblDeceased.MonthOfBurial, tblDeceased.YearOfBurial, tblDeceased.Inscription AS tblDeceased_Inscription, tblDeceased.pp, tblDeceased.Notes, tblDeceased.Links, tblDeceased.map, tblDeceased.notes_2, [tblDeceased]![Notes] & " " & [tblDeceased]![notes_2] AS [Complete Notes], tblDeceased.pdfMap, tblDeceased.GraveNotes, CDate([DayOfBurial] & "/" & [MonthOfBurial] & "/" & [YearOfBurial]) AS FullDateofBurial, CDate([DayOfDeath] & "/" & [MonthOfDeath] & "/" & [YearOfDeath]) AS FullDateofDeath, tblMemorial.Mlink, tblMemorial.Inscription
FROM tblMemorial LEFT JOIN (tblDeceased INNER JOIN tblJoin ON tblDeceased.GraveID = tblJoin.fkGraveID) ON tblMemorial.MemorialID = tblJoin.fkMemorialId
ORDER BY tblDeceased.Forenames;

My logic was that this woud show me all records from the tbl memorials

However I get an error that cofused me about inner and outer joins

The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement.

I've searched google and it seems to be a common error but I can't find a solution

thanks

Ian