why doesn't this query work?

[Ann Glovre]

It keeps saying C2.ABN not defined

Company table key: ABN
CompanyRoute key: RouteN & ABN

SELECT C1.Name, C2.Name
FROM Company AS C1, Company AS C2
WHERE C1.ABN (SELECT C1.ABN FROM Company as C1 inner join
CompanyRoute ON C1.ABN = CompanyRoute.ABN)
And C2.ABN(SELECT C2.ABN FROM company as C2 inner join
CompanyRoute ON C2.ABN = CompanyRoute.ABN)
And C1.ABN <> C2.ABN;

Thanks

 

[[MVP] S. Clark]

Shouldn't

WHERE C1.ABN (SELECT C1

be something like

WHERE C1.ABN [IN|EXISTS|=|etc] (SELECT C1

Also,

And C2.ABN(SELECT

be

And C2.ABN [IN|EXISTS|=|etc] (SELECT

?


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Van T. Dinh]

I don't follow the part:

C1.ABN
( SELECT C1.ABN FROM Company as C1 inner join
CompanyRoute ON C1.ABN = CompanyRoute.ABN )

I think you left out some test operator(s) here (comparison test / set
membership test / quantification test). Similarly for the C2.ABN
expression.

Also, it looks to me that you got TWO copies of the Table [Company] with the
SAME alias [C1] and TWO copies of the Table [Company] with the SAME alias
[C2]. JET may get confused with the duplicated aliases.