Solved

composite primary keys and relationships

Posted on 2013-06-22
5
446 Views
Last Modified: 2013-06-22
hey guys, i've got this table called CodeDefinition and i'm trying to link it to this other table called CodeStorage. CodeDefinition has a compsite primary key and here's what i see in the Indexes window.
CodeDefintion TableCodeDefinition's indexes Window
in the index window it clearly states the the index is unique. however when i try to join the relationship from CodeDefintion.Cat1 to CodeStorage.Cat1, this is the error message i get.

Relationship joining Error
so i go look at the field properties of the table and here's what i see
CodeDefinition's Field Properties
i think it's strange cause the indexes window says that the indexes are all Unique but the field properties says it's not even indexed.

1) why is there a difference between indexes window and field properties
2) how do i fix this?

if i go to the field properties and change the index field from no to yes (no duplicates) then an extra index is added to the table as per the below picture and i can form a relationship. is this the right way to solve it? i think the main problem is how come the indexes window shows a index for the primary key but the field properties doesn't show

fix
3) guys an additional question is - i guess i'm trying to join 2 tables using a composite primary key. it is made up of Cat1, Cat2 and Cat3. what is the right way to join tables using a composite primary key?
0
Comment
Question by:developingprogrammer
[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
  • 3
  • 2
5 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39267973
<<1) why is there a difference between indexes window and field properties>>

 It's different because there is not an index on that individual field.

<<2) how do i fix this? >>

  Join on all three fields or create an index on Cat1 and then join on that if that is what forms the relationship.

<<3) guys an additional question is - i guess i'm trying to join 2 tables using a composite primary key. it is made up of Cat1, Cat2 and Cat3. what is the right way to join tables using a composite primary key?>>

 You have to join on all three fields of the Key, not just one of them.

 And FYI; anytime you have repeating fields in a table, it's usually a sure sign that the table design is incorrect.

Jim.
0
 

Author Comment

by:developingprogrammer
ID: 39267982
Whoa cool!!!! I just discovered that my relationship can join on multiple fields like what you said Jim!! Haha show how super noob I am - but a determined noob!! = ))

Jim was there anything you saw on my tables that was incorrect? I'm in the process of reading the relationships article by jdeffman. Really helps a lot! = )
0
 

Author Comment

by:developingprogrammer
ID: 39267997
Jim just completed your article on PKs!! It's really really helpful!! Now I know how to design my tables properly = )
0
 
LVL 58
ID: 39268080
<<Whoa cool!!!! I just discovered that my relationship can join on multiple fields like what you said Jim!! Haha show how super noob I am - but a determined noob!! = ))>>

 We all start at some point; I'm just getting C# and .Net under my belt.

<<Jim just completed your article on PKs!! It's really really helpful!! Now I know how to design my tables properly = ) >>

 The key article just covered keys.  If you want to get into normalization (proper table design), you might want to spend some time on this:

HOW TO: Introduction to Application & Database Design .
http://www.youtube.com/watch?v=4YH6gG0Y660

  It's about an hour, but I think you'd find it worth it.

  Of course there are many good articles, video's, and past threads here on EE covering normalization and table design.    Would be worth it to look around a bit before you dig in again.

Jim.
0
 

Author Comment

by:developingprogrammer
ID: 39268086
Hrmm yea I've actually been through hours of courses (online) into databases, normalisation etc optimisation but I guess it is always quite general and sometimes I miss out on some specific things. Yup I definitely want to check out that video. Thanks Jim for your suggestion and help!! = ))
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

630 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