MS Access SQL Query - Error in Join

Question

i am new to SQL and have been trying to have an SQL query in MS Access to join multiple tables using the below sql. This is for doing access testing.

Have explained what i intent to achieve in bold

SELECT

Table1.Role,
Table1.Object,

Table 1 is the base table which has role, authorization object

Table2.Role,
Table2.User_Name,

Table 2 has the users mapped to the role

Table3.Org_Level_Desp,
Table3.Org_LEvel_Values_1,

Table 3 has controls at organization level, this determine which company or plant the user can access

Table4.Role_Description,

Table 4 has role descriptions for roles mentioned in Table 1

Table5.Full_Name,
Table5.Department,

Table 5 has user name and department. Common field is user name from table 2

FROM
Table1
RIGHT JOIN Table2 ON Table1.Role=Table2.Role
RIGHT JOIN Table3 ON Table1.Role=Table3.Role
RIGHT JOIN Table4 ON Table1.Role=Table4.rOLE

Joining required tabled from Table 2, 3 and 4 to Table 1

FROM 
Table2
RIGHT JOIN Table5 ON Table2.USER_NAME=Table5.USer

Joining required tables from table 5 to table 1

I am getting multiple syntax error for the above query. I think i am missing on something basic - Can anyone help ?

Thanks ! Uday

Seems i cannot add more in comment. Sorry, below is the code that i updated, still getting the same error. Can you please advise ?

SELECT

TABLE1.Role, 
TABLE1.Object, 
TABLE1.Field_name, 
TABLE1.Value, 
TABLE1.[and], 
TABLE1.ID_whether_object_is_deleted, 

TABLE2.Role,
TABLE2.User_Name,
TABLE2.End_date,

TABLE3.Org_Level_Control,
TABLE3.Org_LEvel_Values_1,
TABLE3.Org_LEvel_Values_2,

Table4.Role_Description,

TABLE6.Field_Short_Description,

TABLE7.Object_Level_Desp,

TABLE8.Auth_Obj_Text,

TABLE5.Full_Name,
TABLE5.Department,

TABLE9.Valid_to,
TABLE9.Lock,

from ((((((TABLE1 INNER JOIN TABLE2 ON TABLE1.Role=TABLE2.Role)
INNER JOIN TABLE3 ON TABLE1.Role=TABLE3.Role)
INNER JOIN Table4 ON TABLE1.Role=Table4.role)
INNER JOIN TABLE6 ON TABLE1.Field_Name=TABLE6.Field_Name)
INNER JOIN TABLE7 ON TABLE1.[Object]=TABLE7.Org_Object)
INNER JOIN TABLE8 ON AGR_TABLE1.Field_name=TABLE8.Field_Name)

FROM 
(TABLE2 INNER JOIN TABLE5 ON TABLE2.USER_NAME=TABLE5.[USer])
INNER JOIN TABLE9 ON TABLE2.USER_NAME=TABLE9.[User]);

Below is the code that i updated, still getting the same error. Can you please advise ?

SELECT

TABLE1.Role, 
TABLE1.Object, 
TABLE1.Field_name, 
TABLE1.Value, 
TABLE1.[and], 
TABLE1.ID_whether_object_is_deleted, 

TABLE2.Role,
TABLE2.User_Name,
TABLE2.End_date,

TABLE3.Org_Level_Control,
TABLE3.Org_LEvel_Values_1,
TABLE3.Org_LEvel_Values_2,

Table4.Role_Description,

TABLE6.Field_Short_Description,

TABLE7.Object_Level_Desp,

TABLE8.Auth_Obj_Text,

TABLE5.Full_Name,
TABLE5.Department,

TABLE9.Valid_to,
TABLE9.Lock,

from ((((((TABLE1 INNER JOIN TABLE2 ON TABLE1.Role=TABLE2.Role)
INNER JOIN TABLE3 ON TABLE1.Role=TABLE3.Role)
INNER JOIN Table4 ON TABLE1.Role=Table4.role)
INNER JOIN TABLE6 ON TABLE1.Field_Name=TABLE6.Field_Name)
INNER JOIN TABLE7 ON TABLE1.[Object]=TABLE7.Org_Object)
INNER JOIN TABLE8 ON AGR_TABLE1.Field_name=TABLE8.Field_Name)

You can't have multiple "FROM" statements. To follow your logic above, under your join to "Table4", try RIGHT JOIN Table5 ON Table2.UserName =Table5.User — Davy C
– Davy C, Commented Mar 23, 2016 at 9:47
By the way, you need to stick a colon on the end of the query if you are using MS Access. — Davy C
– Davy C, Commented Mar 23, 2016 at 9:50
you have tagged ms-access, try creating the tables and use the graphical query designer to generate your query. — Krish
– Krish, Commented Mar 23, 2016 at 9:50
@DavyC: you probably meant a semicolon ;, but you don't actually need this. Access appends it when using the query designer, but it's optional. — Andre
– Andre, Commented Mar 23, 2016 at 10:02

Gordon Linoff · Accepted Answer · 2016-03-23 11:02:54Z

1

I have no idea why you would want RIGHT JOIN, particularly if you are learning SQL. Start with INNER JOIN. If that is leaving out rows, then move to LEFT JOIN.

The syntax in MS Access uses parentheses:

SELECT . . .
FROM (((Table1 INNER JOIN
        Table2
        ON Table1.Role = Table2.Role
       ) INNER JOIN
       Table3
       ON Table1.Role = Table3.Role
      ) INNER JOIN
      Table4
      ON Table1.Role = Table4.rOLE
     ) INNER JOIN
     Table5
     ON Table2.USER_NAME = Table5.USer

You can then select the columns you want in the SELECT.

Note that the parentheses would look quite awkward in any other database.

answered Mar 23, 2016 at 11:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Uday Kumar Over a year ago

Thank you.. Appreciate your time for responding to the query. I will try our inner join and see how it works. When i tried the above query, it says "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect" Can you please advise where i am going wrong ?

Gordon Linoff Over a year ago

@UdayKumar . . . user is a reserved word. You should probably name the column something else or put it in square braces.

Andre · Accepted Answer · 2016-03-30 08:55:34Z

1

Re: your updated SQL.

Value is a reserved word in Access SQL, so may be others like Role, Object, Lock. Put these into [square brackets], both in the SELECT and the FROM clause.

And you have an extraneous comma at the end of your SELECT clause:

TABLE9.Valid_to,
TABLE9.Lock,    <== delete this comma

from ((((((TABLE1 INNER JOIN TABLE2 ON TABLE1.Role=TABLE2.Role)

answered Mar 30, 2016 at 8:55

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Collectives™ on Stack Overflow

MS Access SQL Query - Error in Join

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related