CODEWITHSUNDEEP
ms-accessms-access-2007
Matt Hall
Matt Hall

Reputation: 2412

Joining a calculated field to a table within 1 query

I have a field that when shortened to its 2 leftmost characters can be joined as a key field to another table.

When I do this I always have to do the LEFT([FieldName],2) bit in a query of its own, and then do the actual join in a second query.

Is there a way of accomplishing this within a single query?

Here's the syntax of the query that shaves off the 2 leftmost characters; let's call it QueryA:

SELECT Left([TableA].[Field1],2) AS AbbrField1
FROM [TableA];

Here's the query that joins QueryA to another table based on that field "AbbrField1" I've had to create first; let's call it QueryB:

SELECT [TableB].[Field2]
FROM [QueryA] 
LEFT JOIN [TableB] ON [QueryA].[AbbrField1] = [TableB].[Field1];

Upvotes: 1

Views: 5275

Answers (1)

Taryn
Taryn

Reputation: 247700

You should be able to JOIN the tables using the LEFT function on the JOIN:

SELECT B.[Field2]
FROM [TableA] as A
LEFT JOIN [TableB] as B 
    ON Left(A.[Field1], 2) = B.[Field1];

This will remove the need for the second query.

Note: this was tested in MS Access 2003.

Upvotes: 3

Related Questions