Joining two tables on similar data (not exact)

[dugoneill]

In my current system I have absolutely no control over two other sources which I need to map together based on items not being an exact match, but I do know the pattern of the differences (namely one system for what ever reason sometimes adds an "S" prefix to the front of a character string which should map to record in the other system which doesn't include this prefix.

In SQL Server, I am able to do this within the join

FROM A join B on A.No = 'S'+B.No

I then do an update to make the serial numbers match once I know they are both referencing the same item (different data aspects of it in each system which I am combining).

I am trying to use Access 2007 as middleware to but can't come up with a similar way to map the items between the two systems.

 

[MSAccessRookie]

In my current system I have absolutely no control over two other sources which I need to map together based on items not being an exact match, but I do know the pattern of the differences (namely one system for what ever reason sometimes adds an "S" prefix to the front of a character string which should map to record in the other system which doesn't include this prefix.

In SQL Server, I am able to do this within the join

FROM A join B on A.No = 'S'+B.No

I then do an update to make the serial numbers match once I know they are both referencing the same item (different data aspects of it in each system which I am combining).

I am trying to use Access 2007 as middleware to but can't come up with a similar way to map the items between the two systems.

As long as B.No is a string, the above statement should work fine in Access. If B.No is a Number, try using the following instead:

FROM A join B on A.No = 'S'+cStr(B.No)

In addition, if you want all records where there is an entry in both tables, consider using Inner Join instead of just Join.

 

[dugoneill]

As long as B.No is a string, the above statement should work fine in Access. If B.No is a Number, try using the following instead:

FROM A join B on A.No = 'S'+cStr(B.No)

In addition, if you want all records where there is an entry in both tables, consider using Inner Join instead of just Join.

Thanks on both counts. That did the trick.

 

[Atomic Shrimp]

I never knew it was possible to join on an expression, without fist doing it in a separate child query. That's pretty interesting.

The graphical query designer doesn't like it though, and will break any such query if it is opened in design view again, then saved.

 

[dugoneill]

I never knew it was possible to join on an expression, without fist doing it in a separate child query. That's pretty interesting.

The graphical query designer doesn't like it though, and will break any such query if it is opened in design view again, then saved.

That is correct, it can only be done (viewed) in SQL View.