Newbie question - Help creating a primary key

[Guest]

I am creating a database to track performance of equipment by week and to
compare that to budgeted performance by week. Each piece of equipment has a
unique cost center number but that cost center number is entered for every
week so I can't use cost center as the primary key. Also, the week number is
entered for every cost center so I can't use that field. Auto numbering
won't work because the actual performance and the budgeted performance
tables need to be related by the same key and it is likely that the
information will never be entered in the same order. The combination of the
cost center and week number would be unique, however. I know I can highlight
both fields at the same time in the table design and assign a primary key
but I have learned never to take anything for granted. My question is if I
create a key in the way I just described am I creating a key that combines
both fields (which is what I think I want) or am I just creating two
separate primary keys that will trip me up latter. If the latter is the
case, how do create the key I need.

TIA

Steve H

 

[Henry]

Ok! You don't have a great problem. You are not thinking
table normalization. I suggest you research table
normalization processes to solve your problem.
I would suggest a table that contains the cost center
code and discription, using the cost center as the key
for that table. Now you can have the data entry releted
to the cost center. And so on. If you need some
additional info about table normalization send me an
Email and I will send you a few documents that describe
normalization and relationships.

Cheers,
Henry (e-mail address removed)

 

[John Vinson]

I am creating a database to track performance of equipment by week and to
compare that to budgeted performance by week. Each piece of equipment has a
unique cost center number but that cost center number is entered for every
week so I can't use cost center as the primary key. Also, the week number is
entered for every cost center so I can't use that field. Auto numbering
won't work because the actual performance and the budgeted performance
tables need to be related by the same key and it is likely that the
information will never be entered in the same order. The combination of the
cost center and week number would be unique, however. I know I can highlight
both fields at the same time in the table design and assign a primary key
but I have learned never to take anything for granted. My question is if I
create a key in the way I just described am I creating a key that combines
both fields (which is what I think I want) or am I just creating two
separate primary keys that will trip me up latter. If the latter is the
case, how do create the key I need.

You are not creating two primary keys: you are creating ONE Primary
Key, which consists of two fields. Either field alone can have
multiple values but the combination of the two will be unique.

As Henry suggests, if a Cost Center is a real meaningful entity in
your system, you should (and may already have) a Cost Center table
with the Cost Center ID as its primary key and other fields describing
it.

An Autonumber *will* work - but not the way you're thinking! Users
should generally never even *see*, much less type, the value of an
Autonumber field. It works behind the scenes. Typically for a one to
many relationship you could have the "one" side table (perhaps here
just the Cost Center, or a table with one row per cost center/week
combination) on the main form, and a Subform containing the equipment
entered for that center. This "many" side table would have a Long
Integer foreign key linked to the Autonumber via the Subform's
Master/Child Link Field property. You can also store the Cost Center
and week number as a two-field foreign key in the third table though
it can be a bit more complex than using the surrogate autonumber key.