Access-SQL: Inner Join with multiple tables

Question

i have multiple tables in a database:

tblOjt

ID    studentid    courseid    companyid    addresseeid    dateadded    datestarted    dateended    ojthours

1         3            1           1             1         9/25/2013                                  500

tblStudent

ID    lastname    firstname    middlename    course    gender    renderedhours    dateadded    archive

3     Dela Cruz      Juan        Santos       BSIT      Male          500

tblCourse

ID    coursealias    coursename                                            hours
1         BSIT      Bachelor of Science in Information Technology          500

tblCompany

ID    companyname

1      MyCompany

tblAddressee

ID    addresseename

1     John dela Cruz

i need to have a SQL statement in which i can get this values:

tableOjt.id  tableOJT.surname,firstname, and middlename  course  companyname  addresseename dateadded datestarted dateended ojthours

how will i get this code in SQL using those join methods...im writing it in VB6 ADODC, is this the same syntax in a standard SQL ? thanks

Yawar · Accepted Answer · 2017-12-14T19:19:30.190

73

If you are writing a query against an Access database backend, you need to use the following join syntax:

select
  t1.c1
, t2.c2
, t3.c3
, t4.c4
from ((t1
inner join t2 on t1.something = t2.something)
inner join t3 on t2.something = t3.something)
inner join t4 on t3.something = t4.something

The table and column names aren't important here, but the placement of the parentheses is. Basically, you need to have n - 2 left parentheses after the from clause and one right parenthesis before the start of each new join clause except for the first, where n is the number of tables being joined together.

The reason is that Access's join syntax supports joining only two tables at a time, so if you need to join more than two you need to enclose the extra ones in parentheses.

edited Dec 14 '17 at 19:19

answered Oct 14 '13 at 22:38

Yawar

11,272
4
48
80

hi, i tried your approach: SELECT tblOjt.id, tblStudent.firstname, tblStudent.middlename, tblStudent.lastname, tblStudent.course, tblCompany.companyname, tbAddressee.addressee, tblOjt.dateadded, tblOjt.datestarted, tblOjt.dateended, tblOjt.ojthours FROM ((tblOjt INNER JOIN tblStudent ON tblOjt.studentid = tblStudent.id) INNER JOIN tblCompany ON tblOjt.companyid = tblCompany.id) INNER JOIN tblAddressee ON tblOjt.addresseeid = tblAddressee.id; however, if i run it in SQL , msAccess asks me to enter parameter value to tblAddressee.addressee .. i can't work it out perfectly.. – Kay Singian Oct 15 '13 at 01:36
/tblAddressee.addresseename .that's what msAccess is asking me to enter a parameter vale – Kay Singian Oct 15 '13 at 01:51
1

figured it out, i dont have tblAddressee.addresseename , i have tblAddressee.addressee thanks for the info :) – Kay Singian Oct 15 '13 at 01:57
Good to know for DBase III x MySQL heads like me. :) – zx81 May 14 '14 at 04:06
1

I don't understand why the SQL Server engineers don't have a word on this. It is kind of ridiculous to have two different syntax for two applications that belong to the same software proprietary. Nevertheless, it works for me. Thanks! – Cavaleiro Apr 13 '17 at 08:53
Hi @Yawar -- I have read many posts regarding the use of parenthesis as you have mentioned. However, I am having another issue I am hoping you may know the solution to. I have two left joins, and the second left join is joining on two column pairs (table A -> table C, and table B -> table C). The query runs fine with only one column pair. But as soon as I add the second one (it doesn't matter which field pair), I get "Join expression not supported." Would you maybe know why? – Isaac Moore Aug 02 '18 at 19:52
@IsaacMoore can you open a new question? It would be easier to answer there, especially with a code sample and resulting message. – Yawar Aug 02 '18 at 19:58
Hi @Yawar, thank you for the response. I have done so [here](https://stackoverflow.com/questions/51661579/nested-join-in-excel-vba-adodb-results-in-join-expression-not-supported). Thank you! – Isaac Moore Aug 02 '18 at 20:21
@Yawar , Thank you very much for your simplified explanation of the linking status in Access You saved me, my dear I have been trying to join 3 tables and the process fails every time without knowing the reason – Silver Hawk Apr 02 '21 at 13:00

score 2 · Answer 2 · edited Oct 23 '13 at 19:44

SELECT tblOjt.id, tblStudent.firstname, tblStudent.middlename, 
       tblStudent.lastname, tblStudent.course, tblCompany.companyname, 
       tblAddressee.addressee 
FROM (((tblOjt 
     INNER JOIN tblStudent ON tblOjt.studentid = tblStudent.id) 
     INNER JOIN tblCourse ON tblOjt.courseid = tblCourse.id) 
     INNER JOIN tblCompany ON tblOjt.companyid = tblCompany.id) 
     INNER JOIN tblAddressee ON tblOjt.addresseeid = tbladdressee.id

found it!thanks to Yawar's approach...

score 0 · Answer 3 · edited Sep 17 '16 at 10:49

been trying to run this SQL using VBA but won't run using DoCmd.RunQuery. I've tried the SQL and it's working though.

str = "SELECT tbl_company.[Company], tbl_company.[Commodity], tbl_company.[Segment], tbl_company.[MainProduct]," & _
      " tbl_financials.[DataYear]," & _
      " mstr_financial.[FinancialData]," & _
      " tbl_financials.[Amount]," & _
      " tbl_financials.[Unit]," & _
      " tbl_company.[CompanyID]" & _
      " FROM (tbl_company" & _
      " INNER JOIN tbl_financials ON tbl_company.[CompanyID] = tbl_financials.[CompanyID])" & _
      " INNER JOIN mstr_financial ON tbl_financials.[FinID] = mstr_financial.[FinID] " & _
      " ORDER BY tbl_company.[Company], tbl_financials.[DataYear] DESC"

Access-SQL: Inner Join with multiple tables

3 Answers3

Linked

Related