Access Query link two tables with similar values-CodePudding

I am trying to create a select query in access with two tables I want to link/create a relationship.

Normally, if both tables contains same value you can just "drag" and create a link between those two columns.

In this case however, the second table have an " /CUSTOMER" added at the end in the fields.

Example; Table1.OrderNumber contains order numbers which always contains 10 characters

Table2.Refference contains same order numbers, but will have a " /CUSTOMER" added to the end.

Can I link/create a relationship between these two in a Query? And how?

Thanks for the help! Sebastian

CodePudding user response：

Table1.OrderNumber contains order numbers which always contains 10 characters

If so, try this join:

ON Table1.OrderNumber = Left(Table2.Reference, 10)

CodePudding user response：

For these nuanced joins you will have to use SQL and not design view with diagram. Consider the following steps in MS Access:

In Design view, create the join as if two customer fields match exactly. Then run the query which as you point out should return no results.
In SQL view, find the ON clause and adjust to replace that string. Specifically, change this clause
```
ON Table1.OrderNumber = Table2.Refference
```
To this clause:
```
ON Table1.OrderNumber = REPLACE(Table2.Refference, '/CUSTOMER', '')
```
Then run query to see results.

Do note: with this above change, you may get an Access warning when trying to open query in Design View since it may not be able to be visualized. Should you ignore the warning, above SQL change may be reverted. Therefore, make any changes to query only in SQL view.

Alternatively (arguably better solution), consider cleaning out that string using UPDATE query on the source table so the original join can work. Any change to avoid complexity is an ideal approach. Run below SQL query only one time:

UPDATE Table2
SET Refference = REPLACE(Refference, '/CUSTOMER', '')