MULTIPLE JOIN IN MS ACCESS

Question

SELECT VISA41717.Fraud_Post_Date,
       VISA41717.Merchant_Name_Raw,
       VISA41717.Merchant_City,
       VISA41717.Merchant_Country,
       VISA41717.Merchant_Category_Code,
       VISA41717.ARN,
       VISA41717.POS_Entry_Mode,
       VISA41717.Fraud_Type,
       VISA41717.Local_Amt,
       VISA41717.Fraud_Amt,
       VISA41717.Purch_Date,
       VISA41717.Currency_Code,
       VISA41717.Cashback_Indicator,
       VISA41717.Card_Account_Num,
       BIN.BANK,
       BIN.COUNTRY
FROM ((VISA41717 AS VS)
     LEFT JOIN MASTERCARD_VISA AS MV ON VS.ARN = MV.MICROFILM_NUMBER)
     RIGHT JOIN BIN AS B ON LEFT(Card_Account_Num, 6) = B.INT
WHERE VISA41717.ARN IS NULL
      OR MASTERCARD_VISA.MICROFILM_NUMBER IS NULL
ORDER BY VISA41717.ARN;

I tried this code but give me an error

JOIN EXPRESSION NOT SUPPORTED

Then, I tried this one:

SELECT VISA41717.Fraud_Post_Date,
       VISA41717.Merchant_Name_Raw,
       VISA41717.Merchant_City,
       VISA41717.Merchant_Country,
       VISA41717.Merchant_Category_Code,
       VISA41717.ARN,
       VISA41717.POS_Entry_Mode,
       VISA41717.Fraud_Type,
       VISA41717.Local_Amt,
       VISA41717.Fraud_Amt,
       VISA41717.Purch_Date,
       VISA41717.Currency_Code,
       VISA41717.Cashback_Indicator,
       VISA41717.Card_Account_Num,
       BIN.BANK,
       BIN.COUNTRY,
       LEFT(Card_Account_Num, 6) AS VBIN
FROM VISA41717,
     BIN
     LEFT JOIN MASTERCARD_VISA ON (VISA41717.ARN = MASTERCARD_VISA.MICROFILM_NUMBER
                                  AND VBIN = B.INT);
WHERE VISA41717.ARN IS NULL
      OR MASTERCARD_VISA.MICROFILM_NUMBER IS NULL
ORDER BY VISA41717.ARN;

Still there's an error

SYNTAX ERROR IN JOIN OPERATOR

I'am trying to create a table from two different tables.

Thank you in advance.

try to remove redundant parentheses in *((VISA41717 AS VS) LEFT JOIN MASTERCARD_VISA AS MV ON VS.ARN = MV.MICROFILM_NUMBER)* — anatol, Apr 25 '17 at 02:47
still error ("SYNTAX ERROR IN LEFT(Card_Account_Num, 6) = B.INT") Can you help me with this code? — be33, Apr 25 '17 at 02:51

Parfait · Answer 1 · 2017-04-25T12:20:04.573

0

Consider adding a LEFT JOIN to the query with tables relative to VISA41717 and even use the string expression directly in the ON clause which is viable in MS Access SQL though expressions with constants are not: ID=7.

However, with such expressions warnings may rise if you attempt to view the query in Design View (assuming you use the GUI program, MSAccess.exe). Do note the required parentheses pairings.

...
(FROM VISA41717
 LEFT JOIN MASTERCARD_VISA
     ON (VISA41717.ARN = MASTERCARD_VISA.MICROFILM_NUMBER))
 LEFT JOIN [BIN]
     ON (LEFT(VISA41717.Card_Account_Num, 6) = [BIN].[INT])

edited Apr 25 '17 at 12:20

answered Apr 25 '17 at 03:51

Parfait

104,375
17
94
125

Hmmm...syntactically this is correct. Please make sure the join columns exist. Check which table holds *Card_Account_Num*. Also, `INT` is a [reserved word](https://support.office.com/en-us/article/Access-2007-reserved-words-and-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe) so needs to be escaped with brackets or back ticks. See edit. – Parfait Apr 25 '17 at 12:23

score 0 · Answer 2 · answered Apr 25 '17 at 06:15

When things get too convoluted, it might help to create a query that delivers the required fields:

SELECT *, LEFT(Card_Account_Num, 6) As Card_Account_Num6
FROM VISA41717
WHERE ARN IS NULL

Now you can use the query GUI designer with this query and table BIN as source.

MULTIPLE JOIN IN MS ACCESS

2 Answers2