What's wrong with this SQL query in Microsoft Access?

Question

I have the following SQL query, made using this question as a guide: SQL query return data from multiple tables

SELECT
    Parameters.[New Parameter Number],
    Parameters.[New Parameter Name],
    Files.[File Names],
    Groups.[Group Names]

FROM
    (((FGEJunction a
      INNER JOIN Parameters b ON a.idParameters = b.idParameters)
      INNER JOIN Groups c ON a.idPrimaryGroup = c.idGroups
        AND a.idSecondaryGroup = c.idGroups)
      INNER JOIN Files d ON c.idFiles = d.idFiles)

WHERE
    Parameters.[New Parameter Number]
      LIKE ([Forms]![Key word search parameters]![ParameterSearchBox].[Text] & "*") OR
    Parameters.[New Parameter Name]
      LIKE ([Forms]![Key word search parameters]![ParameterSearchBox].[Text] & "*") OR
    Files.[File Names]
      LIKE ([Forms]![Key word search parameters]![ParameterSearchBox].[Text] & "*") OR
    Groups.[Group Names]
      LIKE ([Forms]![Key word search parameters]![ParameterSearchBox].[Text] & "*");

When I put it in MS Access and press 'Run', it simply says "Syntax error in FROM clause." It also highlights the word 'Parameters' in the first line of the FROM clause. This word is meant to refer to a table which is named 'Parameters'.

I'm new to SQL and Access and I don't know where the syntax error is.

Access requires parentheses around each join clause, so it will be something like `FROM ((FGEJunction a JOIN Parameters b ON a.idParameters = b.idParameters) JOIN Groups c ON a.idPrimaryGroup = c.idGroups AND a.idSecondaryGroup = c.idGroups) JOIN Files d ON c.idFiles = d.idFiles`. Note that this is an Access requirement and not required in "standard" SQL — D Stanley, Jul 24 '17 at 19:03
@Eric No, I just didn't include the entire query. This is just the FROM clause. I also have a SELECT and a WHERE clause. — , Jul 24 '17 at 19:35
@June7 No, but I tried that first. The query builder creates SQL code that, according to Access, contains syntax errors. I can make a brand new query with the query builder and it will say that it has syntax errors when I try to save it. If I write the query in the SQL tab myself it works fine. I'm just trying to add more tables to the query right now, which is what I'm doing with the FROM clause. — , Jul 24 '17 at 19:46
"Works fine" means the SQL will open in Datasheet? Or you type that into SQL View and can save but not open? Switching to Design View errors? — June7, Jul 24 '17 at 20:10
Works fine means it will give the desired results in Datasheet and also in the form that the query was made for. Switching to Design view and then back to the SQL view (with no changes made in the Design view) changes the SQL code. This code does not run or save because Access says it has a syntax error. Replacing that auto-changed code with the original code makes everything work again with no errors. — , Jul 24 '17 at 20:13
Access SQL requires `JOIN` to be explicit as either `INNER`, `LEFT`, or `RIGHT`. — Parfait, Jul 24 '17 at 21:48
It fixed this error, now it shows another. `Parameters` and `Groups` are [reserved word](https://msdn.microsoft.com/en-us/library/bb208875(v=office.12).aspx) in Access. You must enclose them in [Brackets] wherever you use them, or better: rename these tables. — Andre, Jul 25 '17 at 16:50
You really should use the query designer. I have some doubts about *"The query builder creates SQL code that, according to Access, contains syntax errors."*. Maybe it is put off by `Parameters`, since that's a keyword in Access SQL. This table name is a recipe for disaster. — Andre, Jul 25 '17 at 16:52
Another problem: the `.Text` property of text boxes is only valid when the control has the focus. If you don't run this query in the `OnChange` event of the text box, you should use `.Value`. — Andre, Jul 25 '17 at 17:12
If you have the ability to change the table name, change it from `Parameters` to `tblParameters` so you are not stepping on a reserved word. Also, since you give your table of `Parameters` the alias of `b`, you should use that in place of your table name throughout the query. Thus `Parameters.[New Parameter Number]` should be `b.[New Parameter Number]`. etc... — abraxascarab, Jul 25 '17 at 17:53

score 0 · Accepted Answer · answered Jul 25 '17 at 18:21

0

Parameters is a keyword. If you have a table with that name you need to wrap it with brackets [Parameters].

Also, you have assigned an alias to [Parameters] called b. You then need to use that as the name everywhere else. For example, your select section should look like this...

SELECT
    b.[New Parameter Number],
    b.[New Parameter Name],
    d.[File Names],
    c.[Group Names]

and so on.

answered Jul 25 '17 at 18:21

Jim P.

56
4

How would it know where to find New Parameter Number if I refer to the table as the alias in the SELECT statement? Does SQL not execute from top to bottom...? – Jul 25 '17 at 18:32
1

The query engine parses your query and will figure it out. – Jim P. Jul 25 '17 at 19:09

What's wrong with this SQL query in Microsoft Access?

1 Answers1