Combining (2) INNER JOINS with a LEFT JOIN

Question

.....

Answer 1

I would suggest you create that part of the union query first in a new query in design view, then switch to SQL view to see what the query editor came up with.

---

-Tom. Microsoft Access MVP

Answer 2

-- I am having trouble adding the LEFT JOIN to the below QUERY since it already has 2 INNER JOINS and is a bit more complex than the others
-- I need to add this LEFT JOIN:   LEFT JOIN MasterCodeList ON (PFC_RD_3Level_Flat_Expanded.[Project Code] = MasterCodeList.[New Code])

FROM (New_PFI_Request INNER JOIN PFC_RD_3Level_Flat_Expanded ON New_PFI_Request.[N_Alias] = PFC_RD_3Level_Flat_Expanded.[Project Alias]) INNER JOIN UserInfo ON New_PFI_Request.[Created By] = UserInfo.[ID]

Hicdtakacs1,

It is my (good) habit to surround EVERY join with round brackets. Would it help if you use:

FROM (((New_PFI_Request INNER JOIN PFC_RD_3Level_Flat_Expanded ON New_PFI_Request.[N_Alias] = PFC_RD_3Level_Flat_Expanded.[Project Alias]) INNER JOIN UserInfo ON New_PFI_Request.[Created By] = UserInfo.[ID]) LEFT JOIN ….. ON …..)

Instead of using so many table with about the same field structure, could you use just one table with the corrresponding fields and an additional one to identify the special role (now in different tables) of the record?

Imb.

Answer 3

This worked, but I needed to add another closing paren:

Hi cdtakacs1,

Congratulations! I did not do the calculation of het brackets, just showed the principle.

In my applications I do not use QueryDefs, but instead compose this kind of sql-strings on-the-spot automatically, depending on the context  in which it is needed.

Imb.