Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

composite primary keys and relationships

Posted on 2013-06-22
5
Medium Priority
?
464 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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

971 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