How do I creat a field to combine two fields?

P

Patty Stoddard

I have a table with a SkillID and an Applicant ID. How do I create a field
that combines these two fields? I want to set this new field a the key to
avoid duplicates in the table.
 
K

KARL DEWEY

Create the new fields and run an update query with the following --
[SkillID] & [Applicant ID]
 
L

Lynn Trapp

R

Rick B

NOOOOOOOOOOO.

You don't store redundant information in a table. Will you run an update
query every time you add a new record to your database? How about if you
make a change to one of the fields - what will cause the joint field to
update? That was not a thought-through answer.



To combine them, do so in your forms, queries, and reports. Not the table.

To make sure that there are no records in your table with a duplicate of the
two combined, you need to create a compound index.

You create an index in your table that consists of the two fields combined.

use the help file if you don't know how to do that, or post back.


--
Rick B



KARL DEWEY said:
Create the new fields and run an update query with the following --
[SkillID] & [Applicant ID]

Patty Stoddard said:
I have a table with a SkillID and an Applicant ID. How do I create a field
that combines these two fields? I want to set this new field a the key to
avoid duplicates in the table.
Click to expand...
Click to expand...
 
R

Rick B

Lynn:

Doesn't this break all the rules that we constantly talk about on here? One
field should contain one piece of data....
 
P

Patty Stoddard

Rick is correct. I will be constantly adding records. I want to be able to
track all job skills for a particular applicant without creating duplicates.
For example, John Doe may have clerical skills and technical skills. I want
two records in the database for him. One record has his ApplicantID and the
SkillID associated with clerical. The second record has his ApplicantID and
the SkillID associated with technical. I don't want to be able to duplicate
any of these records. Right now, I have an additional third duplicate record
with his ApplicantID and the SkillId associated with clerical.
 
L

Lynn Trapp

In that case, what you need to do, rather than combining the values into a
single field is to create a combination Unique Index. This will prevent you
from adding duplicate records. The only problem is that you will need to be
sure you don't have any duplicate records in the table already. Otherwise,
Access will prevent you from creating the index.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
L

Larry Linson

. . . create a combination Unique Index.
This will prevent you from adding duplicate
records. The only problem is that you will
need to be sure you don't have any dupli-
cate records in the table already. Otherwise,
Access will prevent you from creating the index.
Click to expand...

Unless there are requirements that we haven't read here, I'd suggest taking
a close look at the design of the DB -- you might well benefit from
additional normalization. Having multiple records for a person with multiple
skills still means you have multiple records for the person. A person table
with information about the indiviual (contact info, address / location,
etc.) and a table related many-to-many with a skills table may well make
lookups and searching simpler when, as is usually the case, you enhance the
application.

Larry Linson
Microsoft Access MVP
 
Top