Access-SQL: Inner Join with Multiple Tables

When dealing with multiple interconnected tables in an Access database, the need arises to retrieve data from multiple sources seamlessly. An effective approach is to utilize inner joins, which allow for data retrieval from multiple tables based on matching values.

To retrieve specific values spanning multiple tables, the following query can be employed:

SELECT
  tblOjt.ID,
  tblStudent.Lastname,
  tblStudent.Firstname,
  tblStudent.Middlename,
  tblCourse.Coursename,
  tblCompany.CompanyName,
  tblAddressee.AddresseeName,
  tblOjt.DateAdded,
  tblOjt.DateStarted,
  tblOjt.DateEnded,
  tblOjt.OjtHours
FROM ((tblOjt
INNER JOIN tblStudent ON tblOjt.StudentID = tblStudent.ID)
INNER JOIN tblCourse ON tblStudent.Course = tblCourse.ID)
INNER JOIN tblCompany ON tblOjt.CompanyID = tblCompany.ID)
INNER JOIN tblAddressee ON tblOjt.AddresseeID = tblAddressee.ID;

This query ensures that rows from the tblOjt table are matched with corresponding rows in tblStudent, tblCourse, tblCompany, and tblAddressee based on the common columns (studentID, course, companyID, and addresseeID, respectively). The results provide a consolidated view of the desired data, providing insights into OJT (on-the-job training) activities across multiple aspects.

The syntax for inner joins in Access-SQL is as follows:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column
INNER JOIN table3 ON table2.column = table3.column
...;

Note that the "INNER JOIN" keyword is used to specify the join type, followed by the target table and the matching criteria. Multiple joins can be chained together to retrieve data from even more tables, using the same syntax. These multiple join statements are enclosed within parentheses to ensure proper execution.

By employing inner joins effectively, developers can retrieve data seamlessly from multiple interconnected tables in an Access database, gaining a comprehensive understanding of data relationships and unlocking valuable insights.