Access Case Sensitive Join Table (Inner Join, Left Join)

This Access tutorial explains how to perform case sensitive join table, such as case sensitive inner join and case sensitive left join.

You may also want to read:Access

StrComp Function to Compare text (case sensitive comparison)

Access Case Sensitive Join Table

In Microsoft Access, comparison of text is not case sensitive. For example, Access considers “AAA” and “aaa” are the same. To compare String in Expression, please refer to my previous post about how to use StrComp Function. The most annoying part of case insensitivity is that when we join two tables, “AAA” and “aaa” can join successfully. In this post I will explain how to prevent this.

Access Case Sensitive Inner Join

In an Inner Join, the two key fields have to be exactly the same in order to display the records.

Let’s say we have two Tables, Table1 and Table 2.

Table1

Name
Apple
Banana
Cat

 

Table2

Name
APPLE
BANANA
Cat

Normally if we join the two Tables

All the results will return because Access is case insensitive.

Query2

Table1.Name	Table2.Name
Cat	Cat
Banana	BANANA
Apple	APPLE

To perform case sensitive Inner Join, make use of StrComp Function to perform a binary comparison (case sensitive comparison). The Expression returns 0 if two names are the same under binary comparison.

Run the Query, only exact matches are returned.

Query2

Table1.Name	Table2.Name
Cat	Cat

Access Case Sensitive Left Join

It is difficult to do a direct Left Join (perhaps there are ways but I just don’t know), instead we can make use of UNION to join two Queries. For UNION, we have to type the SQL directly in Query SQL View if we want to elegantly perform everything in a single Query instead of joining two subqueries.

First Query for Union

For the first Query, we want to return the case sensitive match, using above INNER Join example

SELECT Table1.Name, Table2.Name
FROM Table1 INNER JOIN Table2 ON Table1.Name = Table2.Name
WHERE (((StrComp([Table1].[Name],[Table2].[Name],0))=0))

Table1.Name	Table2.Name
Cat	Cat

Second Query for Union

For the second Query, return the Table1 Name that are not case sensitive match, and put Null for the second column in order to align with the first Query structure

SELECT Table1.Name, Null AS Expr1
FROM Table1
WHERE (((Table1.Name) Not In (Select Name From Table2 WHERE StrComp([Table1].[Name],[Table2].[Name],0)=0)));

Name	Expr1
Apple
Banana

UNION

Finally combine these two Queries into a single Query

SELECT Table1.Name, Table2.Name
FROM Table1 INNER JOIN Table2 ON Table1.Name = Table2.Name
WHERE (((StrComp([Table1].[Name],[Table2].[Name],0))=0))
UNION ALL
SELECT Table1.Name, Null AS Expr1
FROM Table1
WHERE (((Table1.Name) Not In (Select Name From Table2 WHERE StrComp([Table1].[Name],[Table2].[Name],0)=0)));

Now we got the result that has the same effect of Left Join Table.

Table1.Name	Table2.Name
Cat	Cat
Apple
Banana