link field

[inungh]

I have to link an existing table which has data format like following
"123-456-789" to my current table format like "123456789". Are there
any work around to link those 2 fields without creating a new field
with same type and update SQL to udpate new field.


Your help is great appreciated,

 

[Michel Walsh]

Depends.


--- If you just want a join, you can use, in SQL view (not supported in
graphical view of a query):


SELECT *
FROM table1 INNER JOIN table2
ON Replace(table1.field1, "-", "" ) = table2.field2



as example (that assumes both fields are text).


You can also do it with the graphical view: bring the two tables and add a
criteria under table2.field2, with:

= Replace([table1].[field1], "-", "" )



Note that in both cases, the Replace removes the "-".


--- If you want to implement a data relation between the two tables, then,
you have to have the same compatible field(s) in each table: either 3 fields
in both tables, either just one field in both tables.





Vanderghast, Access MVP

 

[inungh]

Depends.

--- If you just want a join, you can use, in SQL view (not supported in
graphical view of a query):

SELECT *
FROM table1 INNER JOIN table2
    ON Replace(table1.field1, "-", "" )  = table2.field2

as example (that assumes both fields are text).

You can also do it with the graphical view: bring the two tables and add a
criteria under table2.field2, with:

        = Replace([table1].[field1], "-", "" )

Note that in both cases, the Replace removes the "-".

--- If you want to implement a data relation between the two tables, then,
you have to have the same compatible field(s) in each table: either 3 fields
in both tables, either just one field in both tables.

Vanderghast, Access MVP

I have to link an existing table which has data format like following
"123-456-789" to my current table format like "123456789". Are there
any work around to link those 2 fields without creating a new field
with same type and update SQL to udpate new field.

Your help is great appreciated,- Hide quoted text -

- Show quoted text -

Thanks millions,