Multi-field Primary keys

[Steve S]

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.

 

[Sylvain Lafontaine]

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)

 

[Tony Toews [MVP]]

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/

 

[John W. Vinson]

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).

 

[David W. Fenton]

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.

 

[Byron]

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
(e-mail address removed)

 

[oldblindpew]

Byron,
Did you by chance see my post on Avoiding Redundant Records, and if so,
would this indexing technique apply?

Thanks,
OldBlindPew

 

[Byron]

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

 

[oldblindpew]

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