Dificult Join

[Ter]

I need to join two tables.
One table has the following categories (With example)
Lot ID: CHCH456-01
Name: Circles
Brand: Mike

The second table has this:
Brand: Mike
ID: 456
Site: France

I want the query to bring back LOT ID, Name, Site, and Brand.

Doing a join on BRAND won't work, because a lot of the brands have multiple
sites.
Is there a way to do a join where Lot ID Contains *ID*? Or something
similar. That is the only distinct cahracteristic between the two.

Thanks!

 

[Michel Walsh]

You can use a join on almost any expression which evaluates to a Boolean
value:


SELECT *
FROM a INNER JOIN b
ON a.brand = b.brand
AND a.lotID LIKE "*" & b.id & "*"



It may be required to add ( ) in some cases (as when you use BETWEEN )



Vanderghast, Access MVP