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:
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]
Parameters:
[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 “<>”.
Example:
Table 1: Products
Table 2: Categories
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:
#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
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.