Help with a "like" query between two fields from two different tables...

[KLZA]

I have two tables containing a customer name column.. One is a master
Table with all customer names the other is a regular table with a
field containing similar customer names.. I'd like to create a query
that uses the customer field name from the master table to find like
customer names in the customer field in the regular table. Something
in the query like this:

Field: Customer Table: Master
Like [Regular]![CUSTOMER] & "*"

Is this possible??

 

[Ken Snell]

Something like this, perhaps:

SELECT [Master].*, [Regular].*
FROM [Master], [Regular]
WHERE [Master].[Customer] LIKE
[Regular].[Customer] & "*";

 

[John W. Vinson]

I have two tables containing a customer name column.. One is a master
Table with all customer names the other is a regular table with a
field containing similar customer names.. I'd like to create a query
that uses the customer field name from the master table to find like
customer names in the customer field in the regular table. Something
in the query like this:

Field: Customer Table: Master
Like [Regular]![CUSTOMER] & "*"

Is this possible??

You can actually use LIKE in a JOIN clause (a "non equi join":

SELECT <whatever fields you want>
FROM [Master] INNER JOIN [Regular]
ON [Master].[CUSTOMER] LIKE [Regular].[CUSTOMER] & "*"

Using customer names as keys, or for joins, is A Very Bad Idea, though - names
are not unique (I know three guys named Fred Brown), not stable (many women
and some men change their name on marriage), and nicknames or variant
spellings are very common.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com