Access subquery using Like IN

[mikesjLFG]

I am trying to create a query which will take all values from table
Names Field Pre and compare it against table AllNames field Name.
Problem is there will only be PART of the name in the Allnames field.

What I have attempted was variations of this and I haven't been able
to get it to work with the * operator to show that it would be a
partial match not complete.

Select Allnames.Name
FROM Allnames
WHERE (((Name) Like ((Name) In (Select Pre + '*' From Names))));

If anybody could help me in identifying the issue and resolving it
would be greatly appreciated.

Thanks

 

[Michel Walsh]

Joins are generally (but not always) preferable to sub-queries, and, in this
case, it seems they make your job easier:


SELECT a.Name
FROM allNames AS a INNER JOIN names AS b
ON a.name LIKE b.name & "*"


should do.


Hoping it may help,
Vanderghast, Access MVP

 

[Allen Browne]

Perhaps something like this:

SELECT Allnames.[Name]
FROM Names, Allnames
WHERE Names.Pre Like AllNames.[Name] &* "*";

 

[Stefan Hoffmann]

hi Mike,

Select Allnames.Name
FROM Allnames
WHERE (((Name) Like ((Name) In (Select Pre + '*' From Names))));

Try a FULL JOIN:

SELECT A.Name
FROM Allnames A, Name N
WHERE A.Name LIKE N.Name & "*"

mfG
--> stefan <--

 

[mikesjLFG]

Everyone is absolutly awesome! The suggestions worked great for my
needs and I really do appreciate it!

Thank you
Mike