Performing a join on a calulcated field

[myemail.an]

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 :(

[paii, Ron]

<my********@googlemail.comwrote in message
news:91**********************************@i29g2000 prf.googlegroups.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.

[lyle fairfield]

"my********@googlemail.com" <my********@googlemail.comwrote in
news:91**********************************@i29g2000 prf.googlegroups.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.