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:  Ms Access Outer Join SQL 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 inner join on multiple fields
  • access DB multiple JOINs
  • access multiple fields joins
  • access sql for joining fields
  • excel or access to record waiting list data
  • join on multiple fiels MS access
  • ms access join fields to become one item
  • ms access two joins
Read Also:  Drivers Log Book Template and Sample for Microsoft Access


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

Powershell Query MS Access Database in Microsoft Windows Application
Powershell Query MS Access Database in Microsoft Windows Application
Powershell Access Database – Ms. Powershell is
Microsoft Access Weekday and Week Number Function
Microsoft Access Weekday and Week Number Function
Ms Access Weekday Function is purposed to


Must read×

Top