Forum Discussion
Display an empty field when there is no corresponding record in joined table.
I haven't used Access for 6-7 years and have forgotten some/ of my skills. My tables build a list of names: 1st Christian name, 2nd Christian name, Surname. However, when there is no 2nd Christian na...
Thank you Karl
That produces the error message:
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 have just noticed that the first line of SQL I posted was missing the first line. Here it is in full.
SELECT tjnPerName.PerID, tlkChristianName.Cnm, tlkChristianName_1.Cnm, tlkSurname.Snm
FROM tlkSurname INNER JOIN ((tlkChristianName INNER JOIN ((tlkChristianName AS tlkChristianName_1 INNER JOIN (tjnPerName INNER JOIN tjnNmCnm AS tjnNmCnm_1 ON (tjnPerName.NmID = tjnNmCnm_1.NmID) AND (tjnPerName.NmID = tjnNmCnm_1.NmID)) ON (tlkChristianName_1.CnmID = tjnNmCnm_1.CnmID) AND (tlkChristianName_1.CnmID = tjnNmCnm_1.CnmID)) INNER JOIN tjnNmCnm ON tjnPerName.NmID = tjnNmCnm.NmID) ON tlkChristianName.CnmID = tjnNmCnm.CnmID) INNER JOIN tjnNmSnm ON tjnPerName.NmID = tjnNmSnm.NmID) ON tlkSurname.SnmID = tjnNmSnm.SnmID
WHERE (((tjnNmCnm.CnmOrd)=1) AND ((tjnNmCnm_1.CnmOrd)=2));
That produces the error message:
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 have just noticed that the first line of SQL I posted was missing the first line. Here it is in full.
SELECT tjnPerName.PerID, tlkChristianName.Cnm, tlkChristianName_1.Cnm, tlkSurname.Snm
FROM tlkSurname INNER JOIN ((tlkChristianName INNER JOIN ((tlkChristianName AS tlkChristianName_1 INNER JOIN (tjnPerName INNER JOIN tjnNmCnm AS tjnNmCnm_1 ON (tjnPerName.NmID = tjnNmCnm_1.NmID) AND (tjnPerName.NmID = tjnNmCnm_1.NmID)) ON (tlkChristianName_1.CnmID = tjnNmCnm_1.CnmID) AND (tlkChristianName_1.CnmID = tjnNmCnm_1.CnmID)) INNER JOIN tjnNmCnm ON tjnPerName.NmID = tjnNmCnm.NmID) ON tlkChristianName.CnmID = tjnNmCnm.CnmID) INNER JOIN tjnNmSnm ON tjnPerName.NmID = tjnNmSnm.NmID) ON tlkSurname.SnmID = tjnNmSnm.SnmID
WHERE (((tjnNmCnm.CnmOrd)=1) AND ((tjnNmCnm_1.CnmOrd)=2));
Hi,
Not really surprising. 
Your SQL statement with 6 joins and some tables and contents/contexts I don't know are hard to resolve from a distance.
Maybe you should try to create a new query and first add only the tables and criteria involved to get the 2. C. name. Start with inner joins and then try outer joins until you get the result you want. Learn from this attempt for the real query and/or add the other tables step by step to the new query.
Servus
Karl
****************
Access Forever
Access News
Access DevCon
Access-Entwickler-Konferenz AEK