confused about LEFT JOIN and RIGHT JOIN

[Tara]

Hello,
I need to do two different LEFT JOIN in one query, but cannot figure out how
to write it out. I have tried the following, but keep getting a syntax
error...

SELECT Table1.a, Table1.cn, IIF((Tb.[c]-Lp.[d])<20, Table1.[d]+50, 0) AS Exp1
FROM Table1 LEFT JOIN Table1 AS Tb ON Table1.a=(Tb.a+20)
FROM Table1 RIGHT JOIN Table1 AS Lp ON Table1.a=(Lp.a+10)
WHERE Table1.cn=Tb.cn

Can someone please help me out?
Tara.

 

[Jeff Boyce]

Tara

If you don't regularly speak SQL, a simpler way to get this would be to open
a new query in design view, set it up, then click on the View toolbar button
and check the SQL view to see how Access converts what you are trying to do.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John Spencer]

Access requires that you use parentheses to identify the join "partners" if
you have more than two tables involved. AND you should only have one FROM

SELECT Table1.a, Table1.cn, IIF((Tb.[c]-Lp.[d])<20, Table1.[d]+50, 0) AS
Exp1
FROM (Table1 LEFT JOIN Table1 AS Tb
ON Table1.a=(Tb.a+20))
LEFT JOIN Table1 AS Lp ON Table1.a=(Lp.a+10)
WHERE Table1.cn=Tb.cn

Also I see no reason not to include the where clause into the join
SELECT Table1.a, Table1.cn, IIF((Tb.[c]-Lp.[d])<20, Table1.[d]+50, 0) AS
Exp1
FROM (Table1 LEFT JOIN Table1 AS Tb
ON Table1.a=(Tb.a+20) AND Table1.Cn = tb.cn)
LEFT JOIN Table1 AS Lp ON Table1.a=(Lp.a+10)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Tara]

Hi John,
Thanks so much for your help.
I included the where clause into the join as you suggested and I no longer
lose the first two rows.

Tara

Access requires that you use parentheses to identify the join "partners" if
you have more than two tables involved. AND you should only have one FROM

SELECT Table1.a, Table1.cn, IIF((Tb.[c]-Lp.[d])<20, Table1.[d]+50, 0) AS
Exp1
FROM (Table1 LEFT JOIN Table1 AS Tb
ON Table1.a=(Tb.a+20))
LEFT JOIN Table1 AS Lp ON Table1.a=(Lp.a+10)
WHERE Table1.cn=Tb.cn

Also I see no reason not to include the where clause into the join
SELECT Table1.a, Table1.cn, IIF((Tb.[c]-Lp.[d])<20, Table1.[d]+50, 0) AS
Exp1
FROM (Table1 LEFT JOIN Table1 AS Tb
ON Table1.a=(Tb.a+20) AND Table1.Cn = tb.cn)
LEFT JOIN Table1 AS Lp ON Table1.a=(Lp.a+10)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,
I need to do two different LEFT JOIN in one query, but cannot figure out
how
to write it out. I have tried the following, but keep getting a syntax
error...

SELECT Table1.a, Table1.cn, IIF((Tb.[c]-Lp.[d])<20, Table1.[d]+50, 0) AS
Exp1
FROM Table1 LEFT JOIN Table1 AS Tb ON Table1.a=(Tb.a+20)
FROM Table1 RIGHT JOIN Table1 AS Lp ON Table1.a=(Lp.a+10)
WHERE Table1.cn=Tb.cn

Can someone please help me out?
Tara.