MS Access Inner Join on Multiple Fields and Conditions

Ms Access inner join function is another SQL Join function, but unlike Left and Right Join, it will return only the records with matching value from both joined or multiple tables with several conditions. For better understanding on this function, look at the picture:

Ms Access Inner Join-1

Ms Access Inner Join-1

Using Inner Join, the returned result will be “C”.

 

The syntax for INNER JOIN function:

SELECT [field_1], [field_n]

FROM [table_1] INNER JOIN [table_2]
ON [table_1.field_1] compopr [table_2.field_2]

Parameters:

[field_1], [field_n): the name of selected and displayed fields.

[table_1], [table_2]: the name of the tables from which records are combined.

[table_1.field_1], [table_2.field_2]: The names of joined fields. The fields don’t need to have same name, but must have the same data type and contain same criteria.

Compopr: Any relational comparison operator: “=”, “<“, “>”, “<=”, “>=”, or “<>”.

Example:

Table 1: Employee database

Ms Access Inner Join-2

Ms Access Inner Join-2

Table 2: Salary database

Ms Access Inner Join-3

Ms Access Inner Join-3

The Case: Create a query to show the Employee Name, Hire Date and Quit Date, where the employee has receive the pay hike.

#1 With SQL Query:

  1. Go to Create tab > Queries group > Query Design
  2. Close the Show Table pop up
  3. In Design tab, switch view into SQL view
  4. Input the SQL code:
Read Also:  Wedding Planner Template and Checklist for Microsoft Access

SELECT EmployeeID, EmployeeName, HireDate, QuitDate

FROM Employee a INNER JOIN Salary b

ON a.EmployeeID = b.Employee

GROUP BY a.EmployeeID, EmployeeName, HireDate, QuitDate

HAVING Count(b.Employee) > 1;

NOTE: GROUP BY and HAVING clause is used to show the employee with increases salary.

  1. Switch to datasheet view to test your code. If done right, it should be like this:

 

Ms Access Inner Join-4

Ms Access Inner Join-4

 

#2: With Query Design

Ms Access Inner Join-5

Ms Access Inner Join-5

  1. Go to Create tab > Queries group > Query Design
  2. In Show Table dialog box, select Employee table and Salary table, then double click the fields that want to be displayed
  3. Create Relation between two tables.
  4. On Design tab > Show/Hide group > click Totals to activate the aggregate function.
  5. In the query grid, change all of fields the Total rows into Group By, except the b.Employee which Total should be Count.
  6. In b.Employee field, input > 1 in Criteria Row
  7. Click Run and check the result

 

#3: With VBA Code

In VBA Code, the concept of the INNER JOIN function is still the same, however you may need a little adjustment with format and syntax in the code.

Related Access Database

  • Access multiple inner join
  • access 2013 inner join multiple
  • access join on multiple fields
  • inner joint multiple query ms access
Read Also:  Ms Access Subquery in Select Statement Syntax


Access Database Tags: #access inner join example #access inner join multiple tables #access join expression not supported #access join on multiple conditions #access multiple joins #access outer join #ms access inner join on multiple fields #ms access join query #multiple inner join access

MS Access VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access
MS Access DB and MDB Viewer Software
MS Access DB and MDB Viewer Software
Access DB Viewer is the software to
MS Access Validation Rule Lookup Table Examples
MS Access Validation Rule Lookup Table Examples
The Validation Rule in Microsoft Access is


Must read×

Top