Several Pk's and duplicates ok?? WHAT??

[Access_guy49]

Hey All, i'm looking for some help from one of the heavy hitters in the Access Forum. (Keep in mind i'm self-taught in access..never picked up a book on the stuff) So far i have done ok. I'v made many databases and in my opinion i have a firm grasp on the fundamentals.

I have come accross a database that I did not create which i am suppose to work on. I can do the work just find but i'm a little confused on the structure. when i looked at the relationships i noticed that many of the field names in several tables were bold (showing they were set as a primary key) So i looked at the design view of a few of these tables and noted that the primary key was set on 3 different fields. i also noticed that each field allowed duplication. Furthermore i noticed that on at least one of the tables, in the relationship window there was a relationship using the ID field which was a 1 to many. however there was a relationship on the other side of that table still using the ID field that was a many to one. How can a field have a one to many relationship and a many to one?? it can either be duplicated or it can't...right???
Anyways i also tried to duplicate this in a test database and found that when you make a table and then right click at the side of the field you wish to make as Primary key, that works fine, but doing it on another field simply switches the primary key to that field and takes it off the first one.

(as a side bar..is the only way to search this forum to go manually through all the threads to see if you can find what you need? aka...no search tools?? or did i just miss them?)

 

[KenHigg]

I may be missing something here, but if you hold the shift key down while you select two rows and continue to hold it down and right click you can make the fields the 'composite' primary key. And while each field can have duplicate values the combination of the two fileds can not be dublicated in the table.


ken

 

[CraigDolphin]

I think what you're seeing is a compound/composite primary key. This is where the combination of several fields must be unique. Any value in one field may be duplicated within that field, but the values in the other compound key fields must not likewise be identical.

Thus, if you have two fields in a compound key
Field1 Field 2
85.........A
85.........B
85.........C
86.........A

would be fine but
Field1 Field 2
85.........A
85.........B
85.........C
85.........A

...would not.

You should be able to find some information on compound keys by searching the forum or googling.

no search tools?? or did i just miss them?)

You missed them. Look for the linked word 'search', third from the right on the blue bar at the top of the forum (but below the tabbed links)

 

[Access_guy49]

Ok, so that makes sense, the 2 fields work as a single primary key. How do you create a relationship that is both one to many and many to one though?

I have added an image of the relationship i am talking about, hope that will help, the middle table is the one i am refering to, all the fields are identified as indexed duoplicates ok.

 

[KenHigg]

This all looks fine to me - a watershed can have many subwatersheds and a subwatershed can have many watercourses. Not sure what this issue is -



ken

 

[CraigDolphin]

I agree with Ken...this is just a matter on one entity linking to (potentially) many sub-entities, each of which may likewise be linked to (potentially) many sub-sub entities.

 

[Access_guy49]

Watershed CA_ID has many subwatershed CA_ID which means subwatershed CA_ID allows duplicates. Then subwatershed CA_ID is a one to many with watercourses CA_ID. which would mean that CA_ID CAN'T have duplicates. that is where i get confused. how can it be both? apparently it is right, because the guy who made this knows alot more about databases than me. i'm just trying to figure out why that works and how?

 

[KenHigg]

One unique CA_ID/WatershedID record in the Watersheds table can have many CA_ID/WatershedID records in the Subwatersheds table

And

One unique CA_ID/SubwatershedID record in the Subwatersheds table can have many CA_ID/SubwatershedID records in the Watercourses table

whew

ken

 

[Access_guy49]

ooooooooooooooooooooooooooo....

Thanks Ken and Craig for you help and not giving up on what in hindsight was a stupid question.

 

[KenHigg]

Trust me - It was not stupid at all. You were just in need of that light going off
Anyone worth their salt has been there -


ken