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

Join expression not supported

P: 8
I'm trying to do a left join on two fields which don't have the same amount of characters.

I'm using the following:

Expand|Select|Wrap|Line Numbers
  1. SELECT TABLE1.BKGNBR, TABLE2.DOCNBR
  2. FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.BKGNBR = LEFT(TABLE2.DOCNBR,9);
  3.  
When doing this I'm getting JOIN expression not supported.

I really need to take all records on TABLE1 and only the records in TABLE2 taking the first 9 characters to make this join.

Can you help me?
Mar 3 '14 #1
Share this Question
Share on Google+
3 Replies


zmbd
Expert Mod 5K+
P: 5,287
why?
There is a very serious design flaw in your database if you are attempting this method of matching...

Your sentence might look like:

Expand|Select|Wrap|Line Numbers
  1. SELECT TABLE1.BKGNBR, TABLE2.DOCNBR 
  2. FROM TABLE1, TABLE2
  3. WHERE ((Left$([BKGNBR],9)=Left$([DOCNBR],9)));
Although I've not ran this so it may not return exactly what you want....

What I would do, is create two queries with calculated field that returns just the left-9, and then make the join using these queries... you can do this all in one go; however, this might be easier to follow.

How is this different from your other thread: http://bytes.com/topic/access/answer...t-lengths-data
Mar 3 '14 #2

P: 8
Thank you for your answer.
I had lost track of that thread (I apologize I'm very new at this).
It is not different at all, the result I'm trying to achieve is the same.
However the tables were created a long time ago and therefore I don't think they considerated communicating these 2 tables together even though they share crucial data.

Now, I have tried creating queries with calculated fields as you suggested but the response time is way over 180 secs which I'm trying to optimize.

Any ideas?
Mar 3 '14 #3

zmbd
Expert Mod 5K+
P: 5,287
I don't see any solution for you beyond what I've offered other than the most obvious, and perhaps not even possible for you.... start from scratch and rebuild the database properly.

I know the pain... I'm in a company now that due to regulations has to keep the old legacy databases, so as they've grown and moved from one to the next, the old mules have stayed hooked to the train... requiring that often the data is "transferred" in batches from one system to another using mapping, triggers, and code to get things in the right places.

That is perhaps what you might have to do, pull the information from the older system to the new, properly designed database and then push your data back out to the old one.... sort of a kludge I know - but the best I have for you right now.

BTW: Go to your profile settings, there you can set how emails are sent to you when threads that you subscribe to are updated and when you receive PM in your Bytes.com account.

BOL
-z
Mar 3 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.