Establish a Relationship Where Comma Delimited

[NeonSky via AccessMonster.com]

Good Day,

For your consideration I have the following question. Please notice the below
sample datasets....

Table A

Name Confo
Joe 11111
Joe 22222
Joe 33333
Chris 55555
Will 66666

Table B

Name Confo
Joe 22222, 33333
Chris 55555

Final Dataset

Name Confo
Joe 22222
Joe 33333
Chris 55555

What I am trying to do is match the records between tables A and B. Is there
a way to have each value within a comma delimited field be searched for in
another data set?

Thank you for your time and efforts!

 

[Marshall Barton]

For your consideration I have the following question. Please notice the below
sample datasets....

Table A

Name Confo
Joe 11111
Joe 22222
Joe 33333
Chris 55555
Will 66666

Table B

Name Confo
Joe 22222, 33333
Chris 55555

Final Dataset

Name Confo
Joe 22222
Joe 33333
Chris 55555

What I am trying to do is match the records between tables A and B. Is there
a way to have each value within a comma delimited field be searched for in
another data set?

It won't be at all quick and can only be done in SQL view:

SELECT tblA.[Name], tblA.Confo
FROM tblA INNER JOIN tblB
ON "," & tblB.Comfo & "," Like "*," & tblA.Comfo & ",*"

 

[John Spencer]

SELECT A.Name, A.Confo
FROM [Table B] as B INNER JOIN [Table A] as A
ON B.Confo LIKE "*" & A.Confo & "*"

You can;t build this query using the query design view, but must do it
in the SQL View.

YOu could also use the following and build it in query design view.
SELECT A.Name, A.Confo
FROM [Table B] as B , [Table A] as A
WHERE B.Confo LIKE "*" & A.Confo & "*"

-- Add both tables to the query
-- DO NOT join the tables
-- Add the fields you want to see
-- under Confo for table B enter
LIKE "*" & [Table A].[Confo] & "*"

Both of these could give you erroneous matches if table A confo is null
or you have values like 222 and 2222 and 322256. Then 222 could match
2222 and 32256. You could handle that by forcing a leading and trailing
comma = Space on Table B.Confo and tableA.Confo

SELECT A.Name, A.Confo
FROM [Table B] as B INNER JOIN [Table A] as A
ON ", " & B.Confo & ", " LIKE "*, " & A.Confo & ", *"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[NeonSky via AccessMonster.com]

You rock, thanks!

For your consideration I have the following question. Please notice the below
sample datasets....

[quoted text clipped - 24 lines]

a way to have each value within a comma delimited field be searched for in
another data set?

It won't be at all quick and can only be done in SQL view:

SELECT tblA.[Name], tblA.Confo
FROM tblA INNER JOIN tblB
ON "," & tblB.Comfo & "," Like "*," & tblA.Comfo & ",*"

 

[NeonSky via AccessMonster.com]

Thank you for your input!

SELECT A.Name, A.Confo
FROM [Table B] as B INNER JOIN [Table A] as A
ON B.Confo LIKE "*" & A.Confo & "*"

You can;t build this query using the query design view, but must do it
in the SQL View.

YOu could also use the following and build it in query design view.
SELECT A.Name, A.Confo
FROM [Table B] as B , [Table A] as A
WHERE B.Confo LIKE "*" & A.Confo & "*"

-- Add both tables to the query
-- DO NOT join the tables
-- Add the fields you want to see
-- under Confo for table B enter
LIKE "*" & [Table A].[Confo] & "*"

Both of these could give you erroneous matches if table A confo is null
or you have values like 222 and 2222 and 322256. Then 222 could match
2222 and 32256. You could handle that by forcing a leading and trailing
comma = Space on Table B.Confo and tableA.Confo

SELECT A.Name, A.Confo
FROM [Table B] as B INNER JOIN [Table A] as A
ON ", " & B.Confo & ", " LIKE "*, " & A.Confo & ", *"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Good Day,

[quoted text clipped - 28 lines]

Thank you for your time and efforts!