Join on

A

Andy

I have two tables. Table A has a field that lists a 3 digit code. Table B has
a field that may contain somewhere in it's value one of these 3 digit codes.
i.e. Table A has 'RR7' as a value, and the field in Table B may be '3456 RR7
clean'.

I need to report which records within Table B would have any of the codes
from Table A within it's string. How could I write this?
 
K

Ken Sheridan

Try this:

SELECT DISTINCT [Table B].*
FROM [Table B], [Table A]
WHERE [Table B].
Code: 
 LIKE "*" & [Table A].[Code] & "*";

Ken Sheridan
Stafford, England
 
A

Andy

Thanks. I have some records however in Table A where the value is just "Y",
if the code field in Table B is "RY3" it is reporting. The field in Table B
is always 3 digits and these 3 digits must be matched within the Table A
field.

Does this make sense?

Ken Sheridan said:
Try this:

SELECT DISTINCT [Table B].*
FROM [Table B], [Table A]
WHERE [Table B].
Code: 
 LIKE "*" & [Table A].[Code] & "*";

Ken Sheridan
Stafford, England

[QUOTE="Andy"]
I have two tables. Table A has a field that lists a 3 digit code. Table B has
a field that may contain somewhere in it's value one of these 3 digit codes.
i.e. Table A has 'RR7' as a value, and the field in Table B may be '3456 RR7
clean'.

I need to report which records within Table B would have any of the codes
from Table A within it's string. How could I write this?[/QUOTE]
[/QUOTE]
Click to expand...
 
K

Ken Sheridan

Sorry, I got it the wrong way round. Should be:

SELECT DISTINCT [Table B].*
FROM [Table B], [Table A]
WHERE [Table A].
Code: 
 LIKE "*" & [Table B].[Code] & "*";

Ken Sheridan
Stafford, England

[QUOTE="Andy"]
Thanks. I have some records however in Table A where the value is just "Y",
if the code field in Table B is "RY3" it is reporting. The field in Table B
is always 3 digits and these 3 digits must be matched within the Table A
field.

Does this make sense?

[QUOTE="Ken Sheridan"]
Try this:

SELECT DISTINCT [Table B].*
FROM [Table B], [Table A]
WHERE [Table B].[Code] LIKE "*" & [Table A].[Code] & "*";

Ken Sheridan
Stafford, England

[QUOTE="Andy"]
I have two tables. Table A has a field that lists a 3 digit code. Table B has
a field that may contain somewhere in it's value one of these 3 digit codes.
i.e. Table A has 'RR7' as a value, and the field in Table B may be '3456 RR7
clean'.

I need to report which records within Table B would have any of the codes
from Table A within it's string. How could I write this?[/QUOTE]
[/QUOTE][/QUOTE]
 
Top