Comparing Similar columns in multiple tables.

[Kakra]

Hi Everyone,

I am trying to compare/relate two columns in two tables to each other in access, where the results shoud return similarities among both colums from both tables.Even if one column has some parts of it.
Example;
T1 T2
Name Members
John Johnson.kay
mike mike
Daniel Danielson.mic
Richard Richardson

I tried; "like[T1.Name]*" in the criteria section of Members.Need Help pls.

 

[AlanJ10]

Well, I learned something new myself doing this

SELECT T1.Name, T2.Members
FROM T1 INNER JOIN T2 ON T2.Members like '*'+T1.Name+'*';

 

[Kakra]

Thanks, i tried it, but it returned a pop up asking for a parameter value. quiet confused why it is doing so.

 

[AlanJ10]

What parameter is it asking for?

I have made the following assumptions based on your post, so you will need to change these parts of the query as necessary.

Your tables are called T1 and T2
You have a Name field in T1 (that contains John, mike, etc...) and a Members field in T2 (that contains Johnson.Kay, mike, etc...)

 

[Kakra]

It saids "Enter Parameter Value" for TI.Name...

 

[AlanJ10]

It's T1 not TI

 

[Kakra]

That was a typo sorry

 

[AlanJ10]

I suppose I was being a bit optimistic hoping it would be that simple although I'm getting a bit confused now.

Just to confirm, what are your table and field names?

 

[Kakra]

T1 AND T2. The query gave me makes a lot of sense, but just dont know why it is not giving the right results. Really appreciate your replies.

 

[CraigDolphin]

I believe Name is a reserved word in Access and maybe that's throwing things off here....try renaming the field to MyName and modify the sql

SELECT T1.MyName, T2.Members
FROM T1 INNER JOIN T2 ON T2.Members like '*'+T1.MyName+'*';

[EDIT] Fixed first line

 

[AlanJ10]

Glad you could join us Craig as I'm starting to run out of ideas.
I wasn't aware that Name was a reserved word and that query is working ok for me, but I'm using Access 2000, so that may be what's causing the confusion.

I was going to ask if you definitely have a Name field in table T1 and a members field in table T2, because something is causing the query to think that you don't.
It might be worth following Craig's suggestion and see if that fixes it, but make sure you change the T1.Name to T1.MyName in the first row as well.

 

[CraigDolphin]

erk, glad you caught that Alan. SQL is not my strong point but I have seen reserved words mess up things erratically before.

 

[AlanJ10]

Looks like Craig was right, I found this link http://support.microsoft.com/kb/286335 and Name is a reserved word in Access 2002 onwards, which could well be why it's not working for you, but not giving me any problems.

So, if you haven't already, use Craig's idea and change the field name to something else. You might also be able to put [] around Name ie. T1.[Name] as that's fixed a problem for me when I was trying to use an SQL reserved word (but an Access reserved word may be different.

 

[Kakra]

it worked

Thanks guys , i just had to change the name wheew!!