Left join with in-instrin

[Blair]

I have a table called “drawingchange”

DRAW	ECN
1222	001
1222	002
1333	222
1333	223
1333	224
1555	333
5556	334

And another tablecalled “Prodchange”

Prod	ProdECN	Parent
aaa	001, 002	L123-00
bbb	001, 999, 334	L133-00
ccc	333, 002	L562-22
ddd	org	L566-11
eee	Org, 001	L966-22
fff	224, 001	L321-11
ggg	334	L562-11

I would like to Left join / in-string search….show all “drawingchange” records and any matches from ECN to ProdECN

DRAW	ECN	Prod	ProdECN	Parent
1222	001	aaa	001, 002	L123-00
1222	001	bbb	001, 999, 334	L133-00
1222	001	eee	Org, 001	L966-22
1222	001	fff	224, 001	L321-11
1222	002	aaa	001, 002	L123-00
1222	002	ccc	333, 002	L562-22
1333	222
1333	223
1333	224	fff	224, 001	L321-11
1555	333	ccc	333, 002	L562-22
5556	334	ggg	334	L562-11

any ideas?

[lfpm062010]

This is one way.

Change your table "Prodchange" design.

Prod	ProdECN	Parent
aaa aaa	001 002	L123-00 L123-00
bbb bbb bbb	001 999 334	L133-00 L133-00 L133-00
ccc ccc	333 002	L562-22 L562-22
ddd	org	L566-11
eee eee	Org 001	L966-22 L966-22
fff fff	224 001	L321-11 L321-11
ggg	334	L562-11

[Blair]

That was my first thought too…Sadly, that table, for many reasons needs to stay as is.
It’s an EPR table that feeds many other reports and queries within our business.

[June7]

Try:

SELECT drawingchange.*, prodchange.*
FROM drawingchange, prodchange
WHERE (((prodchange.ProdECN) Like "*" & [drawingchange].[ECN] & "*"));

or

Query1
SELECT prodchange.Prod, prodchange.ProdECN, prodchange.Parent, drawingchange.ECN, drawingchange.DRAW
FROM prodchange, drawingchange;

Query2
SELECT Query1.DRAW, Query1.ECN, Query1.Prod, Query1.ProdECN, Query1.Parent, InStr([ProdECN],[ECN]) AS Expr1
FROM Query1
WHERE (((InStr([ProdECN],[ECN]))>0))
ORDER BY Query1.DRAW, Query1.ECN, Query1.Prod;