By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Performing a join on a calulcated field

P: n/a
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 :(
Jan 7 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a

<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.
Jan 7 '08 #2

P: n/a
"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.

Jan 7 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.