Generating primary field values based on other fields in same tabl

[Guest]

I want the primary key in my table to be created by the merging of two other
fields in the same table. For example I have two fields: flower and color.
I want the user to enter each value in the respective field, for example Rose
Red, Rose Yellow, Daisy White etc. and then the the primary value of
RoseRed, RoseYellow, DaisyWhite would be automatically entered into the
primary field.

Can I do this?
Any help is greatly appreciated!

 

[Arvin Meyer [MVP]]

You CAN do it, but shouldn't. All data in a table, and that includes the
key, should be in non-decomposeable fields. Concatenated keys which can be
changed or any Primary Key that can be changed, poses a risk to the data
integrity. You need to make sure that there are no duplicates, and the the
data reflects the sources to use your schema, and that violates database
normalization rules.

In most cases, Primary Keys should have no particular meaning to the data.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Hi

I just did a google search on Flower - Red - Rose.

I didn't know there were over 450 types.

See Arvin's answer ??

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

 

[Guest]

Thanks, I think I understand what you are saying. I certainly understand the
importance of unique values in the primary field and the need for data
intregrity!

May I ask for a little more advise? Rather than flowers and colors, I have
a table of Mfgers, fabric patterns, and colors. Different patterns have
different size repeats and I need this infomation to calculate required
yardage to manufacture draperies and other fabric products.

My order entry form uses a subform with a fabric field. This field is the
"many side" of the one to many relationship back to the table we are
discussing the primary key for. I want the form to look up the repeat.

I don't understand why the primary field should generally be without
meaning? I often create sometype of code that would result in a unique
combination, such as the 3 elements (Mger, Pattern, Color). Normally I would
enter it as text: MfgPatCol, and then enter each piece of info in its
respective field. It is a bit of redunant data entry; therefore, my idea to
have the system automatically generate the primary key as a result of the 3
fields which together are unique

Thanks so much for the advise.

 

[Smartin]

Thanks, I think I understand what you are saying. I certainly understand the
importance of unique values in the primary field and the need for data
intregrity!

May I ask for a little more advise? Rather than flowers and colors, I have
a table of Mfgers, fabric patterns, and colors. Different patterns have
different size repeats and I need this infomation to calculate required
yardage to manufacture draperies and other fabric products.

My order entry form uses a subform with a fabric field. This field is the
"many side" of the one to many relationship back to the table we are
discussing the primary key for. I want the form to look up the repeat.

I don't understand why the primary field should generally be without
meaning? I often create sometype of code that would result in a unique
combination, such as the 3 elements (Mger, Pattern, Color). Normally I would
enter it as text: MfgPatCol, and then enter each piece of info in its
respective field. It is a bit of redunant data entry; therefore, my idea to
have the system automatically generate the primary key as a result of the 3
fields which together are unique

Thanks so much for the advise.

If you are looking to ensure uniqueness across three columns you can
create a primary key that uses all three columns. No need to add a
redundant column.

In table design select View.. Indexes. Fill out the index properties
window something like this:

Index Name Field Name Sort Order
==============================================
MyIndex Mger Ascending
<leave blank> Pattern Ascending
<leave blank> Color Ascending

Now highlight the row for MyIndex and set Primary=Yes.

HTH

 

[Arvin Meyer [MVP]]

Instead of concatenating the fields, add a unique index which is a compound
index including all three fields. Open the indexes dialog, give the index a
name, then add the 3 fields. While you can make this index the Primary Key,
I'd advise against it. A Primary Key is often used as a Foreign Key in
other tables. If the key had more than a single field, then you will need to
add the additional fields to the secondary tables. This is clumsy and
inefficient. The better design, IMO, is to use an autonumber as a Primary
Key, then construct a unique index on the other 3 fields.

Certain Primary Key fields do have meaning. Take State Abbreviations or
Country Codes for example. Both are unique, small, and efficient and are
excellent candidates for Primary Keys. It would be ridiculous to add yet
another value to a table of states or countries.

There are other reasons to use surrogate keys as Primary Keys. One is that
they are usually machine generated and unlikely to be duplicated, so you
don't have to check for the duplication. Another is in queries. Long
Integers use 4 bytes and require less processor and network resources than
Unicode text which uses 2 bytes per character, or older non-Unicode which
uses 1 byte per character. If I need to retrieve the primary and foreign
keys for a total of 50,000 records, I am looking at 8 byte joins instead of
as much as a 60 byte join for a 3 field 5 character primary key (5
characters x 2 bytes x 3 fields x 2 tables)

Anyway, to make a very long explanation short, there are thorough
explanations in a number of good books on database design. Have a look for
authors Rebecca Riordan, Steven Roman, and Michael J. Hernandez. If you
really want to delve into deep technical explanation look up "The Relational
Model for Database Management" by E.F. Codd and C.J. Date.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Smartin]

Instead of concatenating the fields, add a unique index which is a compound
index including all three fields. Open the indexes dialog, give the index a
name, then add the 3 fields. While you can make this index the Primary Key,
I'd advise against it. A Primary Key is often used as a Foreign Key in
other tables. If the key had more than a single field, then you will need to
add the additional fields to the secondary tables. This is clumsy and
inefficient. The better design, IMO, is to use an autonumber as a Primary
Key, then construct a unique index on the other 3 fields.

Excellent point.

[snip]