Displaying general results in a query

[Guest]

Hello all , I have yet another question.

I have query that connects to two tables. On tableA I have status codes
such as:

MTMSSSTS745
MTMADUP8349

The numbers at the end change frequently but the letters before hand specify
what type of status it is.

On the other tableB I have:

Field1: Field2:
MTMSSSTS Status Code Update
MTMSADUP Address Update



So I want to make a query ends up looking like this.

Field1: Field2:
MTMSSSTS745 Status Code Update
MTMSADUP8654 Address Update


The problem I am have is that the field that would link the two tables
differs in that one has the varying numbers at the end and the other does
not. Is there a way to make the query only look at the first few characters?
Or something to match it against MTMSSSTS*. Thanks very much!

James O

 

[Douglas J Steele]

Try:

SELECT TableA.Field1, TableB.FIeld2
FROM TableA
INNER JOIN TableB
ON TableB.Field2 LIKE TableA.Field1 & "*"

Note that you cannot build this query through the GUI: you must go into the
SQL View (under the View menu when you have the query builder open) and
modify the SQL yourself.

 

[Guest]

You can edit the joins like below. This is the join before editing --
SELECT ALPHA.ALPHA,control
FROM ALPHA LEFT JOIN [ALPHA-MINUS] ON ALPHA.ALPHA = [ALPHA-MINUS].[xx];

This is the edited in SQL view so they are joined on the left four character
of ALPHA-MINUS.xx --
SELECT ALPHA.ALPHA,control
FROM ALPHA LEFT JOIN [ALPHA-MINUS] ON ALPHA.ALPHA =
left([ALPHA-MINUS].[xx],4);

When you view the query in design view you will not see the join and you
will get a warning saying it can not display the join.

 

[Guest]

Thank you guys so much. I knew that there had to be a way to do this.