Creating a key consisting of two columns

[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

 

[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