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 name, I can't get my query to display a blank field (it just omits the record).
Here's my query as SQL and below that in design view. Any help will be appreciated.
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) ON tlkChristianName_1.CnmID = tjnNmCnm_1.CnmID) INNER JOIN tjnNmCnm ON tjnPerName.NmID = tjnNmCnm.NmID) INNER JOIN tjnNmSnm ON tjnPerName.NmID = tjnNmSnm.NmID) ON tlkChristianName.CnmID = tjnNmCnm.CnmID) ON tlkSurname.SnmID = tjnNmSnm.SnmID
WHERE (((tjnNmCnm.CnmOrd)=1) AND ((tjnNmCnm_1.CnmOrd)=2));
3 Replies
Hi,
If the 2nd Christian name comes from tlkChristianName_1:
In design view double click on the JOIN line between tjnNmCnm_1 and tlkChristianName_1 and in the dialog choose the option that will show all records from tjnNmCnm_1 and only those from the other table that blabla. This changes the INNER JOIN to an OUTER JOIN.
Depending on what tjnNmCnm_1 is, you may need to do the same for the JOIN between tjnPerName and tjnNmCnm_1 and/or change its criteria.
Servus
Karl
****************
Access Forever
Access News
Access DevCon
Access-Entwickler-Konferenz AEK- 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));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
