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:  How to Create or Solve MS Access Enter Parameter Value

#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 query instr multiple search variables
  • format validation rule access 2016
  • how to create a recipe database in ms access


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

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