Creating a key consisting of two columns


R

Rachel Garrett

Greetings,

I have records which are identified by a unique naming system (up to
six characters), and the numbering system changes each year. A key
consisting of [number,year] would work as a primary key, and in Design
View, I was able to shift-select two columns and make it the primary
key in my table.

Now that I've done that, how can I use this primary key in
relationships to other tables? Do I have to go back to autonumbering
instead?

Thanks,
Rachel
 
Ad

Advertisements

J

John W. Vinson

Greetings,

I have records which are identified by a unique naming system (up to
six characters), and the numbering system changes each year. A key
consisting of [number,year] would work as a primary key, and in Design
View, I was able to shift-select two columns and make it the primary
key in my table.

Now that I've done that, how can I use this primary key in
relationships to other tables? Do I have to go back to autonumbering
instead?

Thanks,
Rachel
That's your choice. You can create two fields of matching datatypes and sizes
in your child tables, and join both fields in the Relationships window (drag
the number to the number, drag the year to the year, be sure both lines are
selected/dark, and check the Enforce Referential Integrity checkbox; if you
desire to do so, also check the Cascade Updates and/or Deletes).

You can also use two fields (or ten for that matter) in the Master/Child link
fields of a Subform.

Alternatively, you can just put a unique Index (not a primary key) on the two
fields, add an Autonumber as a primary key, and link it to Long Integer fields
in your child tables.

This can be a contentious issue among Access developers - some folks always
use an Autonumber, others will use a "natural key" such as you describe, and
some have strong opinions about the advantages of the two approaches. I use
both. I'll admit that maintaining multifield joins can be a hassle, but it
does have the advantage that you can display the year/number in Reports based
on child tables without the overhead of a join.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top