Need help with Like operator in MSAccess Query

G

Guest

I'm trying to compare two name fields in two different tables. I want the
results to show up in a query. The names in the tables won't be exactly the
same so I thought I would use the Like operator. For example:

Like "*" & "ABCD" & "*"

where Table 1 Name field may have "Company ABCD". Table 2 Name field may
have "ABCD Company". I'm not sure if this expression will work. To
complicate things, I'd really like Access to cycle through each name record
in the two tables and compare them without me having to type in each
individual name. I don't know where I should start or if this is even
possible.

Thanks in advance.
 
T

tiger0268 via AccessMonster.com

Tina said:
I'm trying to compare two name fields in two different tables. I want the
results to show up in a query. The names in the tables won't be exactly the
same so I thought I would use the Like operator. For example:

Like "*" & "ABCD" & "*"

where Table 1 Name field may have "Company ABCD". Table 2 Name field may
have "ABCD Company". I'm not sure if this expression will work. To
complicate things, I'd really like Access to cycle through each name record
in the two tables and compare them without me having to type in each
individual name. I don't know where I should start or if this is even
possible.

Thanks in advance.

Little unsure of what you want, but maybe you should try the following:

Like "*ABCD*"

Hope it helps.
 
B

Bob Barrows [MVP]

Tina said:
I'm trying to compare two name fields in two different tables. I
want the results to show up in a query. The names in the tables won't
be exactly the same so I thought I would use the Like operator. For
example:

Like "*" & "ABCD" & "*"

where Table 1 Name field may have "Company ABCD". Table 2 Name field
may have "ABCD Company". I'm not sure if this expression will work.

Why wouldn't it? You ARE talking about using this expression in a WHERE
clause, right?
To complicate things, I'd really like Access to cycle through each
name record in the two tables and compare them without me having to
type in each individual name. I don't know where I should start or
if this is even possible.

Stop now. It is not possible. You want to create an inner join using an
unspecified portion of a field to provide the join ...? Ain't gonna happen.

You will need to use a couple recordsets in a VBA module to accomplish this.

Of course, if you simply needed to remove the word "Company" from all the
names before comparing them, you could try this (I'm not sure this will be
supported in Access ... )

.... FROM table1 t1 inner join table2 t2 on
trim(replace(t1.[name],'Company','')) =
trim(replace(t2.[name],'Company',''))
 
J

John Vinson

I'm trying to compare two name fields in two different tables. I want the
results to show up in a query. The names in the tables won't be exactly the
same so I thought I would use the Like operator. For example:

Like "*" & "ABCD" & "*"

where Table 1 Name field may have "Company ABCD". Table 2 Name field may
have "ABCD Company". I'm not sure if this expression will work. To
complicate things, I'd really like Access to cycle through each name record
in the two tables and compare them without me having to type in each
individual name. I don't know where I should start or if this is even
possible.

Thanks in advance.

You can create a (non-updateable) Cartesian join query using the WHERE
clause to link them... but having Access automagically determine which
part of the name field is important and which is "filler" will be very
difficult or impossible. I suppose you could write some VBA code to
strip off "Inc.", "Co.", "Company", "And Company", "Ltd.", and so on
and so on - but it's going to be monstrous complicated and slow, and
almost sure to give you erroneous or missed joins.

Unfortunately, this is probably a task for which human brains are
still the optimum process!

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top