Geek Answers Handbook

SQL - Joining is not supported

I am using Access and trying to modify this query:

SELECT 
   DateDiff("d", [Invoice]![TxnDate],[ReceivePaymentLine]![TxnDate]) AS ActualPaymentDays, 
   IIF ([ActualPaymentDays] < 90, 0.10, IIF ([ActualPaymentDays] < 120, 0.09, IID ([ActualPaymentDays] , 365, 0.05, 0))) AS PayPerValue
FROM 
   ReceivePaymentLine 
INNER JOIN 
   Invoice ON ReceivePaymentLine.AppliedToTxnTxnID = Invoice.TxnID

I make the table "ActualPaymentDaysRate":

ActualPaymentDay1
PayPerValue1
ActualPaymentDay2
PayPerValue2
ActualPaymentDay3
PayPerValue3

And I modify the request above to be this request:

SELECT 
    DateDiff("d",[Invoice]![TxnDate],[ReceivePaymentLine]![TxnDate]) AS ActualPaymentDays, 
    IIF ([ActualPaymentDays] < [ActualPaymentDaysRate.ActualPaymentDay1],[ActualPaymentDaysRate.PayPerValue1], IIF ([ActualPaymentDays] < [ActualPaymentDaysRate.ActualPaymentDay2], [ActualPaymentDaysRate.PayPerValue2], IIF ([ActualPaymentDays] < [ActualPaymentDaysRate.ActualPaymentDay3],[ActualPaymentDaysRate.PayPerValue3], 0))) AS PayPerValue
FROM 
    ActualPaymentDaysRate, ReceivePaymentLine 
INNER JOIN 
    Invoice ON ReceivePaymentLine.AppliedToTxnTxnID = Invoice.TxnID

It shows the error "JOIN expression is not supported."

0
source share
3 answers

The syntax for combining styles and the new style cannot be combined with n-match.

Add another connection:

SELECT DateDiff("d",[Invoice]![TxnDate],[ReceivePaymentLine]![TxnDate]) AS ActualPaymentDays, IIF ([ActualPaymentDays] < [ActualPaymentDaysRate.ActualPaymentDay1],[ActualPaymentDaysRate.PayPerValue1],IIF ([ActualPaymentDays] < [ActualPaymentDaysRate.ActualPaymentDay2],[ActualPaymentDaysRate.PayPerValue2], IIF ([ActualPaymentDays] < [ActualPaymentDaysRate.ActualPaymentDay3],[ActualPaymentDaysRate.PayPerValue3], 0))) AS PayPerValue
FROM ReceivePaymentLine
INNER JOIN Invoice ON ReceivePaymentLine.AppliedToTxnTxnID = Invoice.TxnID
JOIN ActualPaymentDaysRate ON {some condition}

In addition, you specified JOIN as "JON" in your request. I fixed it in this.

+1
source

What you are doing wrong uses the ANSI SQL-89 syntax and the ANSI SQL-92 syntax for JOIN

INNER JOIN, ANSI SQL-89, , ,

SELECT <column list>
FROM ActualPaymentDaysRate, ReceivePaymentLine

ANSI SQL-92, JOIN, ON,

 INNER JOIN Invoice ON ReceivePaymentLine.AppliedToTxnTxnID = Invoice.TxnID

. , , INNER JOIN .

ANSI SQL-89 , , , , , .

SELECT <column list>
FROM ActualPaymentDaysRate, ReceivePaymentLine 
--  WHERE <condition> excluded , NOT a INNER JOIN anymore but still no error

SQL-92, .

+1

Your problem is that you specified two table names in the inner join, you need two inner joins to join the 3 tables.

Watch this.

SELECT DateDiff("d",[Invoice]![TxnDate],[ReceivePaymentLine]![TxnDate]) AS ActualPaymentDays, 
IIF ([ActualPaymentDays] < [ActualPaymentDaysRate.ActualPaymentDay1],[ActualPaymentDaysRate.PayPerValue1],
IIF ([ActualPaymentDays] < [ActualPaymentDaysRate.ActualPaymentDay2],[ActualPaymentDaysRate.PayPerValue2], 
IIF ([ActualPaymentDays] < [ActualPaymentDaysRate.ActualPaymentDay3],[ActualPaymentDaysRate.PayPerValue3], 0))) AS PayPerValue
FROM ActualPaymentDaysRate INNER JOIN ReceivePaymentLine 
ON <Match Condition>
INNER JOIN Invoice 
ON ReceivePaymentLine.AppliedToTxnTxnID = Invoice.TxnID
0
source

More articles:


All Articles