Solved

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

Posted on 2016-10-19
6
45 Views
Last Modified: 2016-10-20
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.



Thanks
fordraiders
0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 250 total points
ID: 41850867
I'd use this in the SQL
SELECT t1.*
FROM Table1 t1 INNER JOIN Table2 t2 ON
      LEFT(t2.MyKey2,10) = LEFT(t1.MyKey1,10)

Kelvin
0
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 41850932
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.
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 total points
ID: 41851071
You can use this simple SQL which is viewable in the designer:

SELECT 
    Table1.*,
    Table2.*
FROM 
   Table1,
   Table2
WHERE
   LEFT(Table1.MyKey1,10) = Table2.MyKey2;

Open in new window


/gustav
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:fordraiders
ID: 41852624
pat, always enjoy the comments. Unfortunately I inherit things i cant change.
But I agree.

dp
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41852626
thanks all
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 41852635
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question