How do I join on a function in MS Access?

Question

I am trying to add a function to my JOIN condition in an MS Access query.

As a simplified example, consider that I have:

tblChildand tblParent and tblGrandparent

tblChild has both a nullable ParentId and GrandparentId depending on which one is known. I'm trying to write a query to take the more specific relationship provided. If this were TSQL it would be:

SELECT
    columns
FROM
    tblChild AS c
LEFT OUTER JOIN
    tblParent AS p
        ON p.[Id] = c.[ParentId]
LEFT OUTER JOIN
    tblGrandparent AS gp
        ON gp.[Id] = COALESE(p.[GrandParentId], c.[GrandParentId])

but I can't figure out how to get a COALESCE style function into the JOIN condition in Access. Here is what I would have expected to work:

SELECT
    columns
FROM
    (tblChild AS c
LEFT OUTER JOIN
    tblParent AS p
        ON p.[Id] = c.[ParentId])
LEFT OUTER JOIN
    tblGrandparent AS gp
        ON gp.[Id] = Nz(p.[GrandParentId], c.[GrandParentId])

Answer 1

I would expect NZ() to work. But can you use or instead?

SELECT columns
FROM (tblChild AS c LEFT JOIN
      tblParent AS p
      ON p.[Id] = c.[ParentId]
     ) LEFT JOIN
    tblGrandparent AS gp
    ON (gp.[Id] = p.[ParentId] or gp.[Id] = c.[GrandParentId]);

This isn't exactly the same. Formally, the last condition would be:

    ON (gp.[Id] = p.[ParentId] or gp.[Id] = c.[GrandParentId] and gp.[Id] <> p.[ParentId]);

EDIT:

Another approach is two joins:

SELECT columns
FROM ((tblChild AS c LEFT JOIN
       tblParent AS p
       ON p.[Id] = c.[ParentId]
      ) LEFT JOIN
     tblGrandparent AS gp
     ON (gp.[Id] = p.[ParentId])
    ) LEFT JOIN
    tblGrandparent AS gp2
    ON gp.[Id] = c.[GrandParentId]);

You then have to adjust the "columns" to pull from either gp or gp2 as appropriate.