?
Solved

MS Access Syntax Error - Join expression not supported

Posted on 2009-04-15
11
Medium Priority
?
1,125 Views
Last Modified: 2012-05-06
This is the query;

Select rs_type from roster left outer join roster_legs on (rs_bookno = rl_bookno and rl_leg = rs_leg) where rl_travelid = 12345 and rs_confirmed is not null and (rs_type = 'Passenger' or rs_type = 'Roundtrip' or rs_type = 'Freight') order by rs_bookno, rs_id

Before people ask me what the data model is - that is irrelevant! I need to know whether the SYNTAX is correct. The record 12345 DOES exist in the database. rl_* fields belongs to 'roster_legs' table and rs_* belongs to 'roster' table
0
Comment
Question by:tfsln
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24152876
test this

Select rs_type from roster left  join roster_legs on (rs_bookno = rl_bookno and rl_leg = rs_leg) where rl_travelid = 12345 and rs_confirmed is not null and rs_type In('Passenger' , 'Roundtrip' ,'Freight') order by rs_bookno, rs_id
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 24152889
The syntax looks correct - reformatted:

SELECT rs_type

FROM roster
    LEFT OUTER JOIN roster_legs
        ON (rs_bookno
                = rl_bookno
        AND rl_leg
                = rs_leg)

WHERE rl_travelid
                = 12345
        AND rs_confirmed is not null
        AND (rs_type
                = 'Passenger'
        OR rs_type
                = 'Roundtrip'
        OR rs_type
                = 'Freight')

ORDER BY rs_bookno,
    rs_id

But you might need the table names like so:

SELECT Table1.*


FROM Table1
    LEFT OUTER JOIN Table2
        ON Table1.ID
                = Table2.ID
        AND Table1.FIELD1
                = Table2.FIELD1;

mx
0
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24152894
"left outer" is the same thing as just "left".  And I don't think Access recognizes both terms, I think Access only recognizes "left".
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:tfsln
ID: 24152897
Nope, still get the same error message...
0
 
LVL 75
ID: 24152937
"And I don't think Access recognizes both terms"

Actually, it does.  See what I posted ... right from the Access query grid.  However, if you save that SQL ... Access removes the word OUTER.  Full OUTER joins are not supported.  A Left / Right Join is assumed to be and OUTER join.

mx
0
 

Author Comment

by:tfsln
ID: 24152950

Ahh yes. In the join parenthesis i had to explicitly refer to the tables like this;

Select rs_type from roster left outer join roster_legs on (roster.rs_bookno = roster_legs.rl_bookno and roster_legs.rl_leg = roster.rs_leg) where rl_travelid = 12345 and rs_confirmed is not null and (rs_type = 'Passenger' or rs_type = 'Roundtrip' or rs_type = 'Freight') order by rs_bookno, rs_id
The above query runs fine with the table names added. It wasnt necessary to add the table names to the fields outside the join parenthesis

0
 

Author Closing Comment

by:tfsln
ID: 31570722
You seem to know your stuff. Maybe you can help me with my other question. Its a simple question, but im getting frustrated with trying to explain it to the guy trying to answer it!

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24323015.html
0
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24153019
You should only 'have' to specify the table name if the field is ambiguous (meaning, the same field name is used in multiple tables).   Not that it's a bad idea to be in the practice of it though.
0
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24153024
mx, If it recognizes "LEFT OUTER" then why does it replace it with "LEFT" ;)
0
 

Author Comment

by:tfsln
ID: 24153038
> You should only 'have' to specify the table name if the field is ambiguous (meaning, the same field name is used in multiple tables).   Not that it's a bad idea to be in the practice of it though.

That has proven to be untrue, because each of our tables is prefixed with a unique 2 letter abbreviation... rs for the roster table and rl for the roster legs. Even without ambiguous names, it still required me to specify the table names and that was the problem with my query.

What you explained is true for sql server, but not access as it turns out.
0
 
LVL 75
ID: 24153073
"mx, If it recognizes "LEFT OUTER" then why does it replace it with "LEFT" ;)'

Create two tables Table1 and Table2   with Fields ID and FIELD1

Open a new query in SQL view and paste this in:

FROM Table1
    LEFT OUTER JOIN Table2
        ON Table1.ID
                = Table2.ID
        AND Table1.FIELD1
                = Table2.FIELD1;

Then open the query in design view ... no errors.  Maybe we should say Access 'allows' it.

mx
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question