Sat. May 25th, 2024
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 called Left Outer Join is the SQL function to join two tables, but only return the result of first table and the matches one with the second table.
Look at the picture below for easier understanding:

Ms Access Left Join-1
Ms Access Left Join-1

If we using Left Join function, the result will be: A, B, and C. The D, E and F from Table 2 will not be returned.

The syntax for Left Join Function:
SELECT [field_1], [field_n]
FROM [table_1] LEFT JOIN [table_2]
ON [table_1.field_1] compopr [table_2.field_2]
[field]: the name of field that selected.
[table]: the name of the joined tables.
[table_n.field_n]: 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: Products

Ms Access Left Join-2
Ms Access Left Join-2

Table 2: Categories

Ms Access Left Join-3
Ms Access Left Join-3

With LEFT JOIN function, we are going to create a query to joined both tables and showing selected fields from both table.

#1 Using SQL Query:
Follow these instructions:
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 ProductID, ProductName, [Price/Unit], StartInvetory, Brands, [Quality Rate]
FROM Products a LEFT JOIN Categories b
ON a.Category = b.CategoryName;
5. Click Run and test your code. If done right, it should be like this:

Read Also:  MS Access Case Statement Syntax in Multiple Conditions
Ms Access Left Join-4
Ms Access Left Join-4

#2: Using Query Design
In Query Design, you do not need to create the code:
1. Go to Create tab > Queries group > Query Design
2. In Show Table dialog box, select Products table and Categories table
3. Just double click the fields that want to be displayed
4. In table list, select the Category field from Products table, drag it to the CategoryName field from Categories table. A relational line will appear.
5. Click Run and check the result

#3: Using VBA Code

Ms Access Left Join-5
Ms Access Left Join-5

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