Solved

composite primary keys and relationships

Posted on 2013-06-22
5
408 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
  • 3
  • 2
5 Comments
 
LVL 57

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 57
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now