SQL Query: Join Tables using two partial string matches

Question

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.

• Mihai: When you say is missing a leading 0,need to match last 9 does it mean you have 10 digits or just 9?
• user34127: No leading zero means booking has 9 numbers

Answer 1

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 );

• user34127: Wow, too easy. A lot like excel.