Solved

composite primary keys and relationships

Posted on 2013-06-22
5
435 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

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

740 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