Hey,
I am trying to nest a RIGHT or LEFT JOIN in an INNER JOIN expression. Unfortunately I cannot figure it out.

My INNER JOIN expression:
SELECT DISTINCTROW [Service Contract Lead].[Hospital Name], [Service Contract Lead].Division, [Service Contract Lead].[System Type], [Service Contract Lead].[System FL], [Service Contract Lead].[Total Value], [Service Contract Lead].[Annual Value], [Service Contract Lead].Commission, [Service Contract Lead].Date, [tblCSE's].EmployeeID, [tblCSE's].LastName, [tblCSE's].FirstName, [tblCSE's].[ASM Last Name]
FROM [tblCSE's] INNER JOIN [Service Contract Lead] ON [tblCSE's].EmployeeID = [Service Contract Lead].[Employee ID];

My LEFT JOIN expression:
SELECT [tblCSE's].[ASM Last Name], [tblCSE's].LastName
FROM [tblCSE's] LEFT JOIN [ASMs]
ON [tblCSE's].[ASM Last Name] = ASMs.LastName;

The query just show the ASMs, who has a Service Contract Lead but I want to see all ASMs in one query. I want to see, if they have generate a lead or not.
Thanks, Conny.