Create a Join using LEFT string manipulation

[kt1978]

Hi

I think I know the answer but want to check. I've been asked to create a query, without querying a query first, but it's the only way I know.

I have two tables

Table1 will have data in a column that is 9 characters long
ULCABC123
ULCABC124
ULCABC125
PLTABC123
PLTABC124

Table2 will have data in a column that is 6 characters long
ULCABC
PLTABC

Question: Can I create a Join from Table2 Field with the Left(Field,6) from Table1


I was thinking something like this. (but then I can't enter design mode)
Query1 - Test

SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Table2 INNER JOIN Table1 ON Table2.ORDDETTYPE = Left(Table1.ORDERDET,6)
GROUP BY  Table2.ORDDETTYPE, Table2.DESCRIPTION;

I presume the only way to do this is first query Table1 (and call Query2) and return the first 6 characters and the create another query (Query3 in this case) using Query2 field joined with Table2 field.

Query2

SELECT Left([ORDERDET],6) AS NEWORDDET
FROM Table1;

Query3

SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Query2 INNER JOIN Table2 ON Query2.[NEWORDDET] = Table2.ORDDETTYPE
GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;

Hope this makes sense.

 

[pbaldy]

Well, you can do the first one, it just can't be represented in the design grid, so you'd have to work in SQL.

 

[kt1978]

Hi


Just thought I'd share another way a colleague suggested doing this... Achieves the same result but seems be tidier.

In Table1 I've created a calculated field NEWFIELD: LEFT(Field,6) and then in Query1 Join the NEWFIELD with the Field in Table2

SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Table2 INNER JOIN Table1 ON Table2.ORDDETTYPE = Table1.[NEWORDERDET]
GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;

Thanks