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:

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

Table 2: Salary database

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:

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.

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

#2: With Query Design

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.