• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

joining two tables on primary key but with just first 10 characters

I had this question after viewing disregard leading zero in query.

Access 2010 vba

I have 2 tables:
table1 primary key    MyKey1
SLS-002213-MAIN-A1   is an example value

table2 primary key    MyKey2
SLS-002213   is an example value

So in order to get the join to work:
I just need the text in MyKey1 to be just 10 characters to match  MyKey2 primary key.

3 Solutions
Kelvin SparksCommented:
I'd use this in the SQL
FROM Table1 t1 INNER JOIN Table2 t2 ON
      LEFT(t2.MyKey2,10) = LEFT(t1.MyKey1,10)

The best solution is to not mush multiple attributes into a single field.  Each column of a table is supposed to be "atomic".  That means that it cannot be further subdivided and still have meaning.  Storing concatenated data in a single field violates first normal form.  

If you haven't gone too far with this design, I would suggest breaking the "key" into it's constituent parts.  Then I would add an autonumber and make it the primary key.  Then I would create a unique index on all the parts of the old PK so you can enforce the business rule of uniqueness.  All child tables would replace the existing FK with an FK that references the new autonumber PK.

However, it is possible that you need to go even further in your normalization process and that would be to create a new table.  The new table would represent the high level part of the old PK and any attributes that relate to it specifically would move with it to the new parent table.  The remaining columns would now be a child table.
Gustav BrockCIOCommented:
You can use this simple SQL which is viewable in the designer:

   LEFT(Table1.MyKey1,10) = Table2.MyKey2;

Open in new window

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

FordraidersAuthor Commented:
pat, always enjoy the comments. Unfortunately I inherit things i cant change.
But I agree.

FordraidersAuthor Commented:
thanks all
Sometimes you can do a restructure with minimal disruption.  You would have to rename the tables and then create a query that joins them back so they look like the original table and you just give the new query the name of the original table.  It's a bit hokey but it might end up saving a lot of effort in the long run.

Anyway, I understand the limitations for existing apps so good luck.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now