CODEWITHSUNDEEP
sqlms-accessjoin
Uday Kumar
Uday Kumar

Reputation: 11

MS Access SQL Query - Error in Join

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)

Upvotes: 1

Views: 129

Answers (2)

Andre
Andre

Reputation: 27634

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)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 1

Related Questions