Using the Like operator with two fields

How do I use the Like operator to join two unidentical tables as below? My main problem is on how to compare the PO filed on tbl2 to the EditNo on tbl1 field. Please see sample tables and expected results below:

tbl1:

         EditNo                     PriceAfter    Status 

     L914; PO2234                    22.00         Yes                       

     l910; po*2240                    38.00         No

Inv Date23/01/21; L919; PO2110       35.29         Waiting

tbl2:

         PO          PriceBefore 

        2234        20.00

        2235        00.00                      

        2240        32.00

        2110        35.00

Expected:

         PO           Status    PriceBefore  PriceAfter  

         2234         Yes       20.00        22.00

         2235         No        00.00        0.00               

         2240         No        32.00        28.00

         2110         Waiting   35.00        35.29

Query:

SELECT * FROM tbl2 INNER JOIN tbl1 ON tbl2.PONo LIKE EditNo.PO;

Answer

You need wildcards:

SELECT *
FROM tbl2 INNER JOIN
     tbl1
     ON tbl1.EditNo LIKE "*" & tbl2.PONo;

The ‘*’ means any character(s).

Advertisement

Answer