If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multi-field Primary keys
Have several tables where the primary key consists of 3 individual fields.
Is there a way to fererence these fields as a group? Right now I have to reference FLD1, FLD2 and fFLD3. It would be nice to just have to code "TBL1.FLDs123=TBL4.FLDS123" instead of "TBL1.FLD1=TBL4.FLD1,,,,etc. If this issue is addressed somewhere in this newsgroup I just missed it or didn't recognize it. Sorry. -- Steve S |
#2
|
|||
|
|||
Multi-field Primary keys
Oh yes, there is a solution and it's very simple: stop using composite keys.
-- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "Steve S" wrote in message ... Have several tables where the primary key consists of 3 individual fields. Is there a way to fererence these fields as a group? Right now I have to reference FLD1, FLD2 and fFLD3. It would be nice to just have to code "TBL1.FLDs123=TBL4.FLDS123" instead of "TBL1.FLD1=TBL4.FLD1,,,,etc. If this issue is addressed somewhere in this newsgroup I just missed it or didn't recognize it. Sorry. -- Steve S |
#3
|
|||
|
|||
Multi-field Primary keys
Steve S wrote:
Have several tables where the primary key consists of 3 individual fields. Is there a way to fererence these fields as a group? Right now I have to reference FLD1, FLD2 and fFLD3. It would be nice to just have to code "TBL1.FLDs123=TBL4.FLDS123" instead of "TBL1.FLD1=TBL4.FLD1,,,,etc. Not to my knowledge. This issue and the parent child fields stuff in subforms is why I switched from using multi field primary keys to autonumber primary keys. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ Granite Fleet Manager http://www.granitefleet.com/ |
#4
|
|||
|
|||
Multi-field Primary keys
On Mon, 18 Jan 2010 12:44:01 -0800, Steve S
wrote: Have several tables where the primary key consists of 3 individual fields. Is there a way to fererence these fields as a group? Right now I have to reference FLD1, FLD2 and fFLD3. It would be nice to just have to code "TBL1.FLDs123=TBL4.FLDS123" instead of "TBL1.FLD1=TBL4.FLD1,,,,etc. If this issue is addressed somewhere in this newsgroup I just missed it or didn't recognize it. Sorry. I'll have to (partially) agree with Sylvain and Tony - in that they're right, there is no way to shortcut this syntax. But I still *very occasionally* use multifield keys (when there's good reason to use cascade updates and the fields are otherwise a good candidate key). -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Multi-field Primary keys
You can set the three fields together as a unique index without using them
collectively as the primary key. This may be old news, but I haven't seen it mentioned in this thread, so I am pointing it out. Steve S wrote: Have several tables where the primary key consists of 3 individual fields. Is there a way to fererence these fields as a group? Right now I have to reference FLD1, FLD2 and fFLD3. It would be nice to just have to code "TBL1.FLDs123=TBL4.FLDS123" instead of "TBL1.FLD1=TBL4.FLD1,,,,etc. If this issue is addressed somewhere in this newsgroup I just missed it or didn't recognize it. Sorry. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201001/1 |
#6
|
|||
|
|||
Multi-field Primary keys
"BruceM via AccessMonster.com" u54429@uwe wrote in
news:a257bdb4c6a78@uwe: You can set the three fields together as a unique index without using them collectively as the primary key. This may be old news, but I haven't seen it mentioned in this thread, so I am pointing it ou Indeed, if it's a candidate key it's obligatory to set the unique index on it -- otherwise you run the risk of allowing bad data to be entered. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#7
|
|||
|
|||
Multi-field Primary keys
Hello Steve,
Bruce is correct, but didn't describe HOW to do it... Up to 10 fields may be conjoined into a unique index and referential integrity applies to the group of fields. This is a sometimes complex technique which should be considered before using it.. This is accomplished by naming the index in table design, Indexes, where there is an Index Name on the first field, and a blank Index name beyond on all the rest, then setting the Unique attribute (not primary, Unique ) to yes. (if theres an "index name" on each field, its a one field index) These fields now act as a group field index and can also be used for referential integrity into other tables (with an identical index structure). I've captured screen shots if you'd like them sent directly to your eMail address. Hope this helps, Byron Downey |
#8
|
|||
|
|||
Multi-field Primary keys
Byron,
Did you by chance see my post on Avoiding Redundant Records, and if so, would this indexing technique apply? Thanks, OldBlindPew "Byron" wrote: Hello Steve, Bruce is correct, but didn't describe HOW to do it... Up to 10 fields may be conjoined into a unique index and referential integrity applies to the group of fields. This is a sometimes complex technique which should be considered before using it.. This is accomplished by naming the index in table design, Indexes, where there is an Index Name on the first field, and a blank Index name beyond on all the rest, then setting the Unique attribute (not primary, Unique ) to yes. (if theres an "index name" on each field, its a one field index) These fields now act as a group field index and can also be used for referential integrity into other tables (with an identical index structure). I've captured screen shots if you'd like them sent directly to your eMail address. Hope this helps, Byron Downey . |
#9
|
|||
|
|||
Multi-field Primary keys
Hi Pew,
I believe it would and can - the unique conjoined key referential integrity rules throttle the cartesian set created by the m-2-m junction table.. I think I see need for, perhaps, a second & third conjoined UNIQUE indexes, but maybe without another m-2-m junction.. I'm also tripping over your use of PolicyID instead of PolicyTypeCode. Without seeing your subject data, I can't describe it other than a unique key having: {PolicyTypeCode & CertID} and another having {PolicyTypeCode & AgrmtID }. Where these keys should live are another issue, but we need clarity on which fields we should be using first, as they may just be indexed table columns rather than m-2-m key junctions. Post an access container of this (with test records) on your Sharepoint and we can work this interactively. Thanks, Byron |
#10
|
|||
|
|||
Multi-field Primary keys
Thanks for your reply, Byron. As per my OP, field names ending in "ID" are
surrogate keys, so PolicyID uniquely identifies an insurance policy in the Policies table. All policies, regardless of type, are in the Policies table. The PolicyTypeCode is a lookup value needed to distinguish the type of policy, e.g., General Liability, Auto Liability, Excess Liability, Worker's Comp, etc. Thus it is a foreign key in the Policies table, but I was planning to use a natural key rather than a surrogate for the parent table. If you have read any of my other posts, you will know I am bogged down trying to redesign my insurance policy tables in a relational manner, rather than flat tables. My database is not really that large, so, although I want to do a proper design, I wonder if I'm getting into overkill. I do not know how much more difficult it will be to write procedures involving all these tables, but I do worry about this aspect. Regrettably, I do not know anything about Sharepoint or access containers, and further, my tables have not yet been created, so I have nothing to post. I suppose at this point I've hijacked this thread, sort of. Should I start a new one? Thanks, OldBlindPew "Byron" wrote: Hi Pew, I believe it would and can - the unique conjoined key referential integrity rules throttle the cartesian set created by the m-2-m junction table.. I think I see need for, perhaps, a second & third conjoined UNIQUE indexes, but maybe without another m-2-m junction.. I'm also tripping over your use of PolicyID instead of PolicyTypeCode. Without seeing your subject data, I can't describe it other than a unique key having: {PolicyTypeCode & CertID} and another having {PolicyTypeCode & AgrmtID }. Where these keys should live are another issue, but we need clarity on which fields we should be using first, as they may just be indexed table columns rather than m-2-m key junctions. Post an access container of this (with test records) on your Sharepoint and we can work this interactively. Thanks, Byron . |
Thread Tools | |
Display Modes | |
|
|