Concatenate

[Fudd]

I have built Excel files that use the Concatenate command to populate a field
with the information from other fields (or parts of info from other fields).
In creating an Access Table I want to creat a field that gets its info from
the first two letters of the info in 3 other fields of the same record.
I would also like this 'created' field to be my Primary Key field. Is that
possible?
thanks
Fudd

 

[tina]

yes it is, but i wouldn't recommend it. first, storing calculated data is a
violation of normalization rules. second, a primary key should be stable; a
pk value that is subject to change when the data from not just one field,
but any one of three fields, changes - which also necessitates changing the
values of all related foreign keys - that sounds like a recipe for trouble.

recommend you use an Autonumber for the table's primary key. as for the
calculated value - which i assume the user/s will need to see - concatenate
the relevant data at runtime wherever you need it...in a query, a form, a
report, etc. as long as the raw field data is correct, the concatenated
value the user sees will always be correct as well.

for a good article on the role of primary keys, see
http://www.dbpd.com/vault/9805xtra.htm

hth

 

[Fudd]

that makes sense - I am learning this on the run - have done some tutorials,
followed them, as in they made sense at the time, but trying to put them to
practical use is causing some frustration
the calculated value was not intended for 'user' use - was thinking I needed
it to create my relationships - so how far off is that thought
I am going to go and read the link you gave me
THANKS

 

[tina]

well, let me be clear: you DO need a primary key for each table that you
will relate to another table. it just shouldn't be a calculated value.

to my mind, relational design principles are the most difficult aspect of
database development to grasp. and the most important, because every other
aspect of the design stems from the base tables/relationships. the good news
is that relational design is relational design, it's not specific to Access;
so once you've learned the principles, you can apply them to other
relational data management systems, as well.

for more information on relational design, go to
http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Steve Schapel]

Fudd,

Another resource that you may find helpful:
http://accesstips.datamanagementsolutions.biz/primarykey.htm