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

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *