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]
Parameters:
[field]: the name of field that selected.

: 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

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:

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

Read Also:  Lookup Wizard in Microsoft Access 2013 and 2016

#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.

Related Access Database

  • access left function vba


Access Database Tags: #how to use left function in ms access #left function in ms access #ms access 2007 left function not working #ms access 2010 query left function #ms access 2010 undefined function left #ms access 2010 vba left function #ms access 2013 left function not working #ms access function call on left-hand side of assignment #ms access left and right function #ms access left function button #ms access left function definition #ms access left function error #ms access left function example #ms access left function in query #ms access left function javascript #ms access left function keys #ms access left function list #ms access left function not available #ms access left function not working #ms access left function python #ms access left function query #ms access left function syntax #ms access left function zero #ms access left instr function #ms access left mid right function #ms access left string function #ms access left trim function #ms access query left function #ms access sql left function #ms access undefined function left in expression #ms access update query using left function #ms access vba left function

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


Must read×

Top