SQL query issue, error on join expression

Question

I have the query below that is causing the following error:

ERROR 3296 (Access)

The ON statment in your Join operation is incomplete or contains too many tables. You may want to put the ON expression in a WHERE clause

SELECT
    TBLCATB.Markis AS [Account #], TBLCATB.[Group Number],
    Max(TBLCATB.Name) AS [Account Name], Sum(TBLCATB.Current) AS [Current],
    Sum(TBLCATB.Thirty) AS Thirty, Sum(TBLCATB.Sixty) AS Sixty,
    Sum(TBLCATB.Ninety) AS Ninety, Max(TBLCATB.[Company Code]) AS [Company Code]
FROM
    TBLCATB
    LEFT JOIN asc_OracleMarkis
        ON CLng(TBLCATB.Markis)=asc_OracleMarkis.Markis
WHERE
    (((TBLCATB.LOB) Like "g*" Or (TBLCATB.LOB) Like "l*" Or
      (TBLCATB.LOB) Like "pb*")) AND
    CLng(tblcatb.markis) not in (select acctnumber from sb_acctinfo)
GROUP BY
    TBLCATB.Markis, TBLCATB.[Group Number]
HAVING
    (((Sum(TBLCATB.Ninety))<>0))
ORDER BY
    Sum(TBLCATB.Ninety) DESC;

How can I avoid this error?

What is the error? What is the database? Welcome to StackOverflow. — Gordon Linoff
– Gordon Linoff, Commented Jan 10, 2013 at 16:35
Hi there & welcome. Gordon is right, you lack some info here. — Melvin
– Melvin, Commented Jan 10, 2013 at 16:39
you are gonna laugh, but its a legacy access database. I migrated some of the tables to SQL Server and now it doesn't like the join. INNER JOIN works oddly enough but the LEFT JOIN doesnt. — user1949586
– user1949586, Commented Jan 10, 2013 at 16:40
@Melvin, what would you need to know? The exact error is ERROR 3296 in MS ACCESS "The ON statment in your Join operation is incomplete or contains too many tables. You may want to put the ON expression in a WHERE clause" — user1949586
– user1949586, Commented Jan 10, 2013 at 16:42
The WHERE filtering conditions are all based on TBLCATB ... asc_OracleMarkis is not involved in the WHERE clause. — HansUp
– HansUp, Commented Jan 10, 2013 at 18:16

Taryn · Accepted Answer · 2013-01-10 16:46:42Z

3

This looks like MS Access syntax and you are missing a closing parantheses:

SELECT TBLCATB.Markis AS [Account #], 
    TBLCATB.[Group Number], 
    Max(TBLCATB.Name) AS [Account Name], 
    Sum(TBLCATB.Current) AS [Current], 
    Sum(TBLCATB.Thirty) AS Thirty, 
    Sum(TBLCATB.Sixty) AS Sixty, 
    Sum(TBLCATB.Ninety) AS Ninety, 
    Max(TBLCATB.[Company Code]) AS [Company Code]
FROM TBLCATB 
LEFT JOIN asc_OracleMarkis 
    ON CLng(TBLCATB.Markis)=asc_OracleMarkis.Markis
WHERE 
(
    (TBLCATB.LOB Like "g*" Or TBLCATB.LOB Like "l*" Or TBLCATB.LOB Like "pb*") 
    AND CLng(tblcatb.markis) not in (select acctnumber from sb_acctinfo)
) <-- this is missing
GROUP BY TBLCATB.Markis, TBLCATB.[Group Number]
HAVING Sum(TBLCATB.Ninety)<>0
ORDER BY Sum(TBLCATB.Ninety) DESC;

edited Jan 10, 2013 at 16:46

answered Jan 10, 2013 at 16:40

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user1949586 Over a year ago

When I add that ')' it now says "Extra ) in query expression

Taryn Over a year ago

@user1949586 see my edit, I stripped all the unneeded )'s from the query.

user1949586 Over a year ago

@bluefeet, thanks still getting the Join is not supported error. The query works in SSMS but stupid Access doesnt like the LEFT JOIN Anymore. Was working fine until I migrated the table.

Taryn Over a year ago

@user1949586 What happens if you remove the CLng() from the join?

Taryn Over a year ago

@user1949586 Do you need the LEFT JOIN you are not using any of the data from that table?

|

James Perrett · Accepted Answer · 2013-01-10 16:51:22Z

0

Rather than using Clng trying either using CAST or CONVERT

answered Jan 10, 2013 at 16:51

James Perrett

764 bronze badges

1 Comment

Fionnuala Over a year ago

SQL is a generic tag, the particular flavour is MS Access. Cast and Convert are not available in MS Access SQL.

Olivier Jacot-Descombes · Accepted Answer · 2013-01-10 17:04:12Z

0

In case your table is a SQL-Server table. Make your query a Pass-Through query and write it in TSQL syntax.

See: pass through query access sql server

answered Jan 10, 2013 at 17:04

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Collectives™ on Stack Overflow

SQL query issue, error on join expression

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related