SQL Query – Join Tables Using Partial String Matches

ms access

Using MS Access, I have to combine two tables where two criteria must be met: Container = Container AND Booking = Booking.

I have two tables where fields are missing a value:

Table1 and Table2 have in common two colunmns:

Container
Booking

Table 2 has additional column "Date". I need to add "Date" to Table1.

Problem is

Container is often missing a "check digit" at the end, so it may be 10 or 11 characters, so i need to match first 10 of container

Booking is often missing a leading zero, so it may be 9 or 10 numbers, so i need to match last 9 of booking.

The following does not work when one or the other Table are missing a leading zero and/or container check digit. How do i adjust SQL?

RIGHT JOIN Table1 ON Table2.[Booking] = Table1.[Booking] AND Table2.[Container] = Table1.[Container];
  • Containers can be reused on new bookings so booking number must also be match.

Best Answer

Not ultra familiar with Access, but I'm pretty sure you have LEFT and RIGHT functions available to you. Something like this seems likely to work:

    ON  RIGHT( Table2.[Booking], 9 ) = RIGHT( Table1.[Booking], 9 )
    AND LEFT( Table2.[Container], 10 ) = LEFT( Table1.[Container], 10 );
Related Question