Left Join & Inner Join

T

Tara

Hello,

I have another question about left join & inner join. The below works for
all the fields except [keys] for which I am asked for the value parametres.

I'm sure my problem has to do with misplaced or missing parenthesis', but
can someone please tell where? I have tried several different options, but so
far, nothing has worked.

SELECT Requête10.a, Requête10.cn, Requête10.Subtotal,
IIf(Requête10.a=10,Requête10.b,[Tb.Subtotal]) AS OpenSubtotal, Table2.p,
Table2.p*13 AS Purse, Ap.Purse AS Keys
FROM ((Requête10 LEFT JOIN Requête10 AS Tb ON (Requête10.cn=Tb.cn) AND
(Requête10.a=(Tb.a+10))) INNER JOIN Table2 ON (Requête10.a = Table2.a) AND
(Requête10.cn = Table2.cn)) LEFT JOIN Table2 AS Ap ON (Table2.cn = Ap.cn) AND
(Table2.a = (Ap.a+10));

Tara
 
J

Jeff Boyce

Tara

One way to trouble-shoot a SQL statement like this is to use Access' query
design window. If you can lay out the query in the design window, Access
will show you (via the View|SQL button) what the SQL looks like.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

Tara

Thanks for your advice. I was finally able to get it sorted out. Part of my
problem was that I was trying to connect things to the wrong tables in SQL.

Jeff Boyce said:
Tara

One way to trouble-shoot a SQL statement like this is to use Access' query
design window. If you can lay out the query in the design window, Access
will show you (via the View|SQL button) what the SQL looks like.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

Tara said:
Hello,

I have another question about left join & inner join. The below works for
all the fields except [keys] for which I am asked for the value parametres.

I'm sure my problem has to do with misplaced or missing parenthesis', but
can someone please tell where? I have tried several different options, but so
far, nothing has worked.

SELECT Requête10.a, Requête10.cn, Requête10.Subtotal,
IIf(Requête10.a=10,Requête10.b,[Tb.Subtotal]) AS OpenSubtotal, Table2.p,
Table2.p*13 AS Purse, Ap.Purse AS Keys
FROM ((Requête10 LEFT JOIN Requête10 AS Tb ON (Requête10.cn=Tb.cn) AND
(Requête10.a=(Tb.a+10))) INNER JOIN Table2 ON (Requête10.a = Table2.a) AND
(Requête10.cn = Table2.cn)) LEFT JOIN Table2 AS Ap ON (Table2.cn = Ap.cn) AND
(Table2.a = (Ap.a+10));

Tara
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

confused about LEFT JOIN and RIGHT JOIN 3
Problems with a Circular Reference 4
Left join is equal to inner join? 5
Inner , Right, Left JOIN 14
Access 2003 INNER JOIN 5
Left Join, Inner Join Nested Query 3
Tricky Inner Join 1
SQL: Inner Join Works, Left Join Does Not 1

Top