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]


[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 “<>”.


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:  MS Access Substring Function

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 database inner join expression not supported
  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1829 iS5HsQOEIB6DMmtMYqhEibfgzj-_kTCHe7P8l1SeJaNIfg1a0lx3L26zxx-OOPa-Dhwo-IVTY0PG7kWBNXWy8Q 89c9a47c18cf9a2fc61d1b696415990a0e8e9445&uuid=&state=_BLhILn4SxNIvvL0W45KSic
  • https://yandex ru/clck/jsredir?from=yandex ru;search;web;;&text=&etext=1838 tw687V24R7y921_8O7UyJjCU0GCBcgL3MMlquI-ZzstS6D8RWNzSbIKl1m1g1HOWdgXXJFts_UqJZcWVg1mn9g 2cbebc1f22bff53249dd5a516a4f7d7369cf35a4&uuid=&state=_BLhILn4SxNIvvL0W45KSic
Read Also:  MS Access Validation Rule Lookup Table Examples

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×