relationship between similar data but not equal

[thread]

Hi all,
is it posible to make a relationship between tables when the data is
similar but not equal for example:
i have this data for example
A1
A2
A3
A4
and i need to make a relationship to a table that have this data
A1-A
A1-B
A1-C
A2-A
and etc...

is it posible to make a relation between the too or i must have a
third table that constracting between the two?

 

[storrboy]

Hi all,
is it posible to make a relationship between tables when the data is
similar but not equal for example:
i have this data for example
A1
A2
A3
A4
and i need to make a relationship to a table that have this data
A1-A
A1-B
A1-C
A2-A
and etc...

is it posible to make a relation between the too or i must have a
third table that constracting between the two?

You'll need a third. In effect your asking a program to make an
educated guess, or to match a pattern. That kind of join
(relationship) would mean that you could effectively relate anything -
the opposite of what a join is supposed to do.

 

[Guest]

Your best option would be to split the column in the second table into two
separate columns, the first containing A1 etc, the second containing A etc.
You can always concatenate them into a single column in a query:

SELECT Column1 & "-" & Column2 AS Column3
FROM Table2;

With the existing columns, you could join the tables:

SELECT <column list>
FROM Table1 INNER JOIN table2
ON LEFT(Table2.Column1,2); = Table1.Column1;

or if the length of the matching strings is variable:

SELECT <column list>
FROM Table1 INNER JOIN table2
ON Table2.Column1 LIKE Table1.Column1 & "*";

or:

SELECT <column list>
FROM Table1 INNER JOIN table2
ON LEFT(Table2.Column1,INSTR(Table2.Column1,"-")-1)
= Table1.Column1;

Splitting the column so that you can do a simple join is far more efficient,
however, and will enable you to create an enforced relationship. You should
be able to split the values form the column into two new columns with a
simple update query, and then delete the original column.

Ken Sheridan
Stafford, England