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:  How to Update Access Database from Ms Excel

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.



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 Subquery in Select Statement Syntax
Ms Access Subquery in Select Statement Syntax
Ms Access Subquery syntax is the Select
MS Access Tab Control in Form Tables or Subform
MS Access Tab Control in Form Tables or Subform
Access Tab Control is mainly used in
MS Access Left Function in SQL Query and VBA Code
MS Access Left Function in SQL Query and VBA Code
Ms Access Left Join function or sometimes


Must read×

Top