how to link two table on part of field

[jscodm]

Hello every one,

I have two tables (A) and (B).

Table (A) contains key-field name "position1".
table (B) contains key-filed name "position2".

Please advise how to make a link between key-field "position1" and part of key-field "position2".

Hope the question is clear..

Thanks in advance.
jscodm

 

[JHB]

You can do it in a query, but not in the relationship window.
It is not a setup I'll recommend.

 

[jscodm]

Thanks JHB for the prompt reply.
I believe the solution could be trough code. could anyone help ?

 

[JHB]

..
I believe the solution could be trough code. could anyone help ?

What solution?

 

[RainLover]

The solution is don't do that.

Your design of the tables in question and I would assume the other tables are all wrong.

Can you post a pic of your relationship window.

 

[Smtgr14]

This relationship can be accomplished using a query. Setup a SELECT query and use an expression to return only what your want from the "position 2" field.

The expression (as a query column) would look something like...
LinkID: Right([Position 2], 4)

which would take the table:
Position 2 | Data1 | Data2
01234567 Some Data
98765432 Other Data

Into:
LinkID | Data1 | Data2
4567 Some Data
5432 Other Data

Then setup a new SELECT query for the fields you want from Table A and the other SELECT query you made and create your relationship (a JOIN) here.

Like everyone else said, this can only be done in a query, not the relationship window.
If you insist on doing this in the relationship window, you need to restructure your tables to include an extra, calculated field, based on the contents of another. Not recommended though.