ERROR in syntax of INNER JOIN clause ... help!

  • Thread starter Thread starter andre
  • Start date Start date
A

andre

I am running a query in Access and I am getting a syntax
error in my JOIN clause. A little background on the odd
SELECT query in the JOIN. I have two tables one called
Accounting, the other called tblDSScenarioResultsPublish.
Accounting does not have a PortfolioID but does have a
CUST_ID. tblDSScenarioResultsPublish has PortfolioID but
not CUST_ID. The Portoflios table correlates the two
(PortfolioID AND Cust_ID). Here is my query:

SELECT ShockedData.*
FROM Accounting
INNER JOIN ShockedData ON
((SELECT PortfolioID FROM Portfolios WHERE ShName =
Accounting.Cust_ID)=ShockedData.PortfolioID)
AND (Accounting.Cusip=ShockedData.Cusip) AND
(Accounting.Lot=ShockedData.Lot)
WHERE (ShockedData.Scenario = -300) AND (Accounting.BkYld
= 4.5)
Click to expand...
AND (Accounting.CurrFace >= 1000000) AND
(Accounting.AvgLife Between 3 AND 6)

The odd thing is that this query runs perfectly on MS SQL
Server. But will not run on linked tables in Access. Is
there a way around this issue?

Thanks a lot,
brazilnut
 
Hi,


Problem of syntax.

Change

FROM Accounting
INNER JOIN ShockedData ON
((SELECT PortfolioID FROM Portfolios WHERE ShName =
Accounting.Cust_ID)=ShockedData.PortfolioID)
AND (Accounting.Cusip=ShockedData.Cusip) AND
(Accounting.Lot=ShockedData.Lot)


to

FROM Accounting As a INNER JOIN

( ShockedData As s INNER JOIN
Portfolios As p ON p.PortfolioID=s.portfolioID)

ON (a.CustID=p.ShName
AND a.Cusip=s.cusip
AND a.lot=s.lot)


Consider the innermost parenthesis, you join s and p, not a, at that moment,
so, exclude all conditions on a, and keep just the conditions involving s
and p. Once done, one level higher, then, speak of conditions involving a
and the other two tables.


Use the same alias, a and s, for the WHERE clause. Using alias may help to
read the overall statement, but is not mandatory, here.



Hoping it may help,
Vanderghast, Access MVP
 
I am running a query in Access and I am getting a syntax
error in my JOIN clause. A little background on the odd
SELECT query in the JOIN. I have two tables one called
Accounting, the other called tblDSScenarioResultsPublish.
Accounting does not have a PortfolioID but does have a
CUST_ID. tblDSScenarioResultsPublish has PortfolioID but
not CUST_ID. The Portoflios table correlates the two
(PortfolioID AND Cust_ID). Here is my query:

SELECT ShockedData.*
FROM Accounting
INNER JOIN ShockedData ON
((SELECT PortfolioID FROM Portfolios WHERE ShName =
Accounting.Cust_ID)=ShockedData.PortfolioID)
AND (Accounting.Cusip=ShockedData.Cusip) AND
(Accounting.Lot=ShockedData.Lot)
WHERE (ShockedData.Scenario = -300) AND (Accounting.BkYld
AND (Accounting.CurrFace >= 1000000) AND
(Accounting.AvgLife Between 3 AND 6)
Click to expand...

A JOIN clause should simply name pairs of fields; it should not be or
contain a SELECT statement. Try

SELECT ShockedData.*
FROM Accounting
INNER JOIN ShockedData ON
Accounting.Cust_ID=ShockedData.PortfolioID
AND Accounting.Cusip=ShockedData.Cusip
AND Accounting.Lot=ShockedData.Lot
WHERE (ShockedData.Scenario = -300) AND (Accounting.BkYld >= 4.5)
AND (Accounting.CurrFace >= 1000000) AND
(Accounting.AvgLife Between 3 AND 6)
 
Hi,


Indeed, seems the ( ) in the ON clause are forbidden, somehow...

SELECT Accounting.*
FROM Accounting As a INNER JOIN

(tblDSScenarioResultsPublish As s INNER JOIN
Portfolios As p ON
(p.PortfolioID=s.portfolioID))

ON a.Cust_ID=p.ShName
AND a.Cusip=s.cusip
AND a.lot=s.lot


I can't check in you exact case, but the following worked, in Northwind:


SELECT p.*
FROM Products AS p INNER JOIN

(Orders As o INNER JOIN [Order Details] As d ON o.OrderID =
d.OrderID)

ON p.unitPrice >= o.freight
AND p.ProductID=d.ProductID



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top