Concatenated key ?

[Suzanne]

Good day, i'm new to Access (but not to db design).
I'm looking to create a table with the key being a concatenation of 3 column
elements: Type&Site&Year
My question: Can Access create this key on the fly as new records are
added? or must the records i'm adding already have a column with the key
compiled; Access stores it and treats it like the primary key.
I do have a book but am unable to find the info there.
Thanks in advance for any help and or luck you care to offer.

 

[Tom van Stiphout]

On Wed, 1 Oct 2008 05:59:01 -0700, Suzanne

You can set the PK at any time: design the table, select the fields,
and click the PK button on the toolbar.
PK fields are required. That goes with the territory.

-Tom.
Microsoft Access MVP

 

[Klatuu]

You define the attributes of an index in table design in Access. You can
use multiple fields for an index.
No action is required when adding records. The indexes are updated
automatically.

 

[Douglas J. Steele]

Just in case it's not evident from the replies Tom and Dave gave you, you
don't need to concatenate the three fields. Since Access allows up to ten
separate fields in a single index, that should be sufficient for your needs.
If for some reason you want the three fields concatenated for display
purposes, add a computed field to a query that concatenates them, and use
the query wherever you would otherwise have used the table.

 

[Jeff Boyce]

Suzanne

Chris offered an alternative to using the three fields as a multi-column
primary key, i.e., creating a unique index on those three and using an
Autonumber primary key field.

Here's one reason why you'll want to consider that approach ... if this
particular table will have its records participating in relationships with
records in any other table(s), having a single-field primary key is less
work (and some would say less confusing) than having to replicate all three
fields together as a composite foreign key.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

I would agree with you, Jeff.
One other consideration to support this method:

Although I have not noticed it in Jet, I have seen where multi column
indexes in SQL Server degrade performance in mass updates, deletes, and
appends because of the additional time to rebuild the indexes. If there are
different data types in the fields, it makes it even worse.

So, the short of it is, if you think you may ever consider upsizing, you may
want to give this method serious consideration.