SQL error Statement, Missing Operator

Question

Is there anything wrong with this statement?

SELECT * 
FROM Movies INNER JOIN
     Sessions
     ON Movies.MovieID=Sessions.MovieID INNER JOIN
     Tickets
     ON Sessions.SessionID=Tickets.SessionID;

When ever I run it on Access I get a Syntax error 'Missing Operator'. Also are there any alternatives to Access that I can import data from an excel spread sheet?

MS Access has a tricky way to join more than 2 tables. Use parentheses somehow. — jarlh, Apr 28 '16 at 11:51

score 2 · Accepted Answer · answered Apr 28 '16 at 11:51

2

In general, no. In MS Access, yes. It likes extra parentheses, probably because the database developers don't believe in readability:

SELECT * 
FROM (Movies INNER JOIN
      Sessions
      ON Movies.MovieID = Sessions.MovieID
     ) INNER JOIN
     Tickets
     ON Sessions.SessionID = Tickets.SessionID;

answered Apr 28 '16 at 11:51

Gordon Linoff

1,242,037
58
646
786

Thanks, I found that out after I did the design version and then went into SQL view and it had extra brackets. Thanks for help. – Simon Apr 28 '16 at 13:54

score 0 · Answer 2 · edited May 23 '17 at 12:31

You could enable OPENROWSET if you have a local instance of SQL, and install MDACs (I would install both x86 and x64 if you have a 64 bit pc). Below is a link to an article that will help you get setup. Also, be sure to run the management studio with elevated privileges.

How to enable Ad Hoc Distributed Queries

Below is how the query would look. In my example I use Excel 8.0 instead of 12 because the column names are addressable in my select statements for 8.

  SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                   'Excel 8.0;Database=C:\Temp\MyExcelDoc.xlsx;', 
                   'SELECT * FROM [Sheet1$]')

score 0 · Answer 3 · answered Apr 28 '16 at 12:06

You can export data from an excel spreadsheet into a number of formats. I find two of the best to be;

Comma Separated Values (CSV)
XML

In many cases, you can deal with the data directly from the Excell spreadsheet. It really depends on what you want to do.

Your SQL query looks fine - but with Access you need to do the extra joins in brackets;

SELECT * FROM ((Movies 
INNER JOIN Sessions ON Movies.MovieID=Sessions.MovieID)
INNER JOIN Tickets ON Sessions.SessionID=Tickets.SessionID)
;

SQL error Statement, Missing Operator

3 Answers3