Modify a join type from "=" to "Like" Is this possible?

[bren11]

I have two separate tables that need linked by a name. The name fields have different input in the tables, so I was wanting to modify the join type to be "like" the records in table b, instead of equal to. I have tried to do this in the SQL window without much luck.

 

[JoeMiller]

Are you saying you want to join on something like this?

TableA.Name
Joe Miller

TableB.Name
Joseph Miller

And have the computer know that they are one and the same? Joe Miller
joe.miller@flotech.net

 

[krsherm]

Use the Query Desgin Grid to test various "like" scenarios. In the criteria cell, just enter the part of the value that you want to match and an asterik to indicate "like". As soon as you tab out of the criteria cell, Access will convert your entry to the proper syntax.

My example below will find every customer in my db whose company name begins with Aqua.

I could make it "ends with Aqua" by changing it to "*Aqua" - or "has Aqua anywhere in the name" by chaning it to "*Aqua*"

SELECT Customers.CompanyName
FROM Customers
WHERE (((Customers.CompanyName) Like "Aqua*&quot)

 

[zambezibill]

NO fuzzy logic in allowed in the relationships.

Tables should be linked on fields that contain non-data related, unique values. These are called key fields and you will have a primary key (in the one side of a one-to-many relationship) and a foreign key (in the many side). For convenience you can use an autonumber field for the primary key.

In other words, set the relationships on otherwise meaningless fields and then you can have the remainder of the data in related records as fuzzy as you want.

TableA.PrimaryKey.Name
1 Joe Miller

TableB.ForeignKey.Name
1 Joseph Miller


Cheers, Bill

 

[grnzbra]

What are you trying to accomplish? Are you dealing with people's names or are you dealing with the names of a set of entities (such as model names of cars from one manufacturer). Fuzzy logic may be all right for a search, but for a join, I don't think so. If you must join on the name, you should run some kind of process to make the various names uniform. Then use the join. (If they are in one database, why do they appear in two places?)

 

[bren11]

Thanks to all those who replied. Yes, I am trying to link a table with name values-ex. Joe Smith to a table with name values J Smith. And there are no other fields to link by. You guys helped me answer my question, no fuzzy logic is allowed in a join. I will concentrate on the suggestion to make the values uniform.

Again, thanks for the help!