A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Multi-field Primary keys



 
 
Thread Tools Display Modes
  #1  
Old January 18th, 2010, 09:44 PM posted to microsoft.public.access.tablesdbdesign
Steve S
external usenet poster
 
Posts: 162
Default 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  
Old January 18th, 2010, 11:06 PM posted to microsoft.public.access.tablesdbdesign
Sylvain Lafontaine[_2_]
external usenet poster
 
Posts: 247
Default 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  
Old January 18th, 2010, 11:26 PM posted to microsoft.public.access.tablesdbdesign
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default 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  
Old January 19th, 2010, 01:02 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old January 19th, 2010, 01:19 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default 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  
Old January 19th, 2010, 09:06 PM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default 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  
Old January 21st, 2010, 11:50 PM posted to microsoft.public.access.tablesdbdesign
Byron[_2_]
external usenet poster
 
Posts: 2
Default 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  
Old January 22nd, 2010, 10:57 PM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default 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  
Old January 23rd, 2010, 04:27 AM posted to microsoft.public.access.tablesdbdesign
Byron[_2_]
external usenet poster
 
Posts: 2
Default 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  
Old January 23rd, 2010, 04:14 PM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.