Performing a join on a calulcated field

Hi all, I have the following problem:


Query1
Code Trimmed Code
AB123 AB
AC123 AC

Table2
AB Europe
AC USA
AD Japan

Desired Output
Code Trimmed Code Region
AB123 AB Europe
AC123 AC USA


I need to join query1 and table2, but the key is a calculated field,
the field "trimmed code" which retrieves the first 2 characters of the
field "code". How can I do a join on a calculated field?

The only solution I can think of is to convert query1 into a table,
and take it from there, but it's clearly not an efficient solution.

Any help would be greatly appreciated!

PS I know this structure of the data sucks: I didn't decide it , I'm
just stuck with it :(

Re: Performing a join on a calulcated field

wrote in message
news:916d7a3a-799b-47f6-8036-281bb6bf6d24@i29g2000prf.google groups.com...
> Hi all, I have the following problem:
>
>
> Query1
> Code Trimmed Code
> AB123 AB
> AC123 AC
>
> Table2
> AB Europe
> AC USA
> AD Japan
>
> Desired Output
> Code Trimmed Code Region
> AB123 AB Europe
> AC123 AC USA
>
>
> I need to join query1 and table2, but the key is a calculated field,
> the field "trimmed code" which retrieves the first 2 characters of the
> field "code". How can I do a join on a calculated field?
>
> The only solution I can think of is to convert query1 into a table,
> and take it from there, but it's clearly not an efficient solution.
>
> Any help would be greatly appreciated!
>
> PS I know this structure of the data sucks: I didn't decide it , I'm
> just stuck with it :(

If you can't fix the table for query 1 by adding a region field, add a join
table with Code and Region fields that would look like the output from
query1.

Re: Performing a join on a calulcated field

"myemail.an@googlemail.com" wrote in
news:916d7a3a-799b-47f6-8036-281bb6bf6d24@i29g2000prf.google groups.com:

> Hi all, I have the following problem:
>
> Query1
> Code Trimmed Code
> AB123 AB
> AC123 AC
>
> Table2
> AB Europe
> AC USA
> AD Japan
>
> Desired Output
> Code Trimmed Code Region
> AB123 AB Europe
> AC123 AC USA
>
>
> I need to join query1 and table2, but the key is a calculated field,
> the field "trimmed code" which retrieves the first 2 characters of the
> field "code". How can I do a join on a calculated field?

I would try:

SELECT Q1.Code, T2.Region
FROM Query1 AS Q1
LEFT JOIN Table2 AS T2
ON Left(Q1.[Trimmed Code],2) = T2.[Code]

OR Maybe
T2.[Code] LIKE Left(Q1.[Trimmed Code],2) & '*'

In the menu for the query builder click the drop down in the upper left
corner and choose the SQL view to write this. Access's query-builder-
wizard-type-dialog is not great for anything but the very simplest of
JOINs.