Building relationship between similar fields

[SamuelOu]

Hi,



I have two tables share a column with similar codes:

Table 1: the codes are as "AB-CDE", "SDF-FDS", "LKJ-SLD"

Table 2: the codes are as "AB-CDE-001", "AB-CDE-002", "SDFFDS-01", "LKJSLD-007"

Is it possible to build relationship among these two fields without VBA programming?

Thank you so much!

[June7]

Review this recent thread about string manipulation https://www.accessforums.net/access/...ent-50262.html

Removing the hyphens would be easy. The number suffix complicates.

[SamuelOu]

Thanks. However, every code has at least two hyphens, such as "AB-CD-E" to "AB-CD-E-001" or "AB-CD-E-02". My goal is removing the last hyphen and anything behind it.

[June7]

Okay, try:

x represents the field name

For table1

Replace(x,"-","")

For table2

Replace(Left(x, InStrRev(x, "-")), "-", "")

A query could be like:

SELECT Table1.*, Table2.* FROM Table1 INNER JOIN Table2 ON Replace(Table1.[fieldname],"-","") = Replace(Left(Table2.[fieldname], InStrRev(Table2.[fieldname], "-")), "-", "");

[SamuelOu]

It works, THANKS A LOT!