CODEWITHSUNDEEP
sqlms-access
Jeff Brady
Jeff Brady

Reputation: 1498

MS Access - Join tables where value is similar if only a LEFT function is used?

Can you join 2 tables if they look like this?

Table 1

Key_ID    Item
1234.P    User 123
4324.P    User 234
5432.P    User 345

Table 2

Key_ID    Item
1234.J    Computer ABC
4324.J    Computer DEF
5432.J    Computer GHI

If I can eliminate the last 2 characters of each Key_ID, the values would be identical. Does something along these lines work?

FROM [Table 1]
    INNER JOIN [Table 2]
    ON Left(([Table 1].Key_ID), Len([Table 1].Key_ID) - 2) =  
        Left(([Table 2].Key_ID), Len([Table 2].Key_ID) - 2)

The values to the left of the period in Key_ID may contain alpha characters, and may be more or less than 4 characters.

Upvotes: 2

Views: 2794

Answers (1)

Mark Hackenberg
Mark Hackenberg

Reputation: 68

I just tried this in Access 2007:

--The lazy way assuming all KEY_ID values are ####.A

SELECT *
FROM Table1 AS T 
INNER JOIN Table2 AS T2 ON LEFT(T.Key_ID,4) = LEFT(T2.Key_ID,4);

--If you want to join everything to the left of the period

SELECT *
FROM Table1 AS T 
INNER JOIN Table2 AS T2 ON Mid(T.Key_ID,1,Instr(1,T.Key_ID,".",1)-1) = Mid(T2.Key_ID,1,Instr(1,T2.Key_ID,".",1)-1);

Upvotes: 2

Related Questions