MS Access SQL : INSERT and INNER JOIN on multiple tables gives missing operator error

Question

I'm new to MS Access SQL and I am trying to JOIN three tables to INSERT a bunch of IDs into another table.

The query I have at the moment is:

INSERT INTO FixturePlayers (FixtureId, PlayerId, TeamId, [Position] )
   SELECT 
      tempFixtureSquad.FixtureId,  Players.PlayerId,  Teams.TeamId, 
      tempFixtureSquad.Position
   FROM 
      tempFixtureSquad
   INNER JOIN 
      Players ON tempFixtureSquad.FirstName =  Players.FirstName 
              AND tempFixtureSquad.LastName = Players.LastName
   INNER JOIN 
      Teams ON tempFixtureSquad.Team = Teams.Team

But when I try to save this, Access says:

Syntax error (missing operator) in query expression ".

Which means sweet nada to me! It even looks like a nonsense message. There is a single " just plonked there at the end... There are no " in my query. and as far as I can tell there doesn't need to be.

Table tempFixtureSquad has columns ID (the PK), FixtureId, Team (the team name), Position, FirstName (string) and LastName (string)

So I need to JOIN this table to tables Players and Teams to get the ID for the player and team for each record in the tempFixtureSquad table as the FixturePlayers table it needs inserting into uses only ID's and has no string columns (columns from that table are listed in the INSERT INTO clause)

What am I doing wrong? Ta

Hmmm, think maybe I need some brackets on that FROM and JOINS? — Toby, Apr 23 '15 at 19:52
I am honestly sorry that you must use MS Access. I've been there and it's not a fun place. It's like Microsoft decide that regular SQL was too hard (false), so they decided to give Access it's own dialect... — openwonk, Apr 23 '15 at 19:58
Note: The term "MS SQL" is often used for Microsoft SQL Server, so that would be a bit misleading in this case. The term for the SQL dialect used in Access seems to be "Microsoft Access SQL": http://stackoverflow.com/questions/22666449/what-is-the-proper-way-to-refer-to-the-sql-dialect-used-by-microsoft-access — Guffa, Apr 23 '15 at 20:04
@openwonk Thanks for the sympathy, I cant flippin stand the thing but helping a mate out... half my battle comes from it being nearly 10 yrs since I last opened Access. For which I am thankful. — Toby, Apr 23 '15 at 20:12
@Guffa, exactly what I meant implicitly. Thanks for making this explicit. — openwonk, Apr 23 '15 at 20:15

score 2 · Accepted Answer · answered Apr 23 '15 at 19:54

2

Try adding parentheses:

FROM (tempFixtureSquad INNER JOIN Players ON tempFixtureSquad.FirstName = Players.FirstName AND tempFixtureSquad.LastName = Players.LastName) INNER JOIN Teams ON tempFixtureSquad.Team = Teams.Team

answered Apr 23 '15 at 19:54

openwonk

14,023
7
43
39

Thanks, that works for the two joins. But If I add a third and continue the brackets idea get `Syntax error in JOIN operation`. These error messages are not the most helpful things ever... – Toby Apr 23 '15 at 20:14
1

Ah, I had misspelled a table name also. Boom. – Toby Apr 23 '15 at 20:17
Please show code. If I had a dime for every time I saw those useless error messages.... – openwonk Apr 23 '15 at 20:18

MS Access SQL : INSERT and INNER JOIN on multiple tables gives missing operator error

1 Answers1