How do I combine the contents of two fields into one in access?

[Thomas]

Need to combine two fields into one in a customer database table, for
example; first name field and last name field need to combine into a new
field called first/last name, for the purpose of creating a primary key field
to avoid duplicate records. Any help would be greatly appreciated!
Thomas

 

[Nikos Yannacopoulos]

Thomas,

Assuming the original table is called tblCustomers, and has two fields
called LName and FName, and you have created a new one called
FirstLastName, then you need to run an update query on it like:

UPDATE tblCustomers SET [FirstLastName] = [LName] & "," & [FName]

This is not a robust solution, though, unless you have some kind of
guarantee that you will only ever have one John Smith... which of course
there isn't! That's why people tables always involve some unique primary
key, even though it may not be meaningful. For instance, in the US, the
standard PK used is the social security number which is guarateed to
exist for every person, and be unique. In other countries when nothing
similar exists, one common workaround is to use just an autonumber
field. Give it some thought...

HTH,
Nikos

 

[Van T. Dinh]

1. People names should never be used as PK. There are different people
with the same name!

2. Assume that it were OK to use names as PK, you are much better off to
leave LastName and FirstName as separate Fields and define the PK as a
multi-Field PrimaryKey (in this case 2-Field PrimaryKey). This way, you
still have the flexibility of showing First-Last or Last-First depending on
the requirements.

 

[geomike]

Nikos desribed best how to do this. However, I would like to add that my
company of 1100 employees has 6 sets of people with the same first and last
name, and coincidentally, three of those sets work in the same departments.
Now this company is spread over 6 geographic regions, but even yet, two of
these personnel had the same supervisor. So, again, the combination of first
and last name may not be the best choice.

 

[Åí˳¾ü]

My name is Peng Shun jun, Why are they english

Thomas,

Assuming the original table is called tblCustomers, and has two fields
called LName and FName, and you have created a new one called
FirstLastName, then you need to run an update query on it like:

UPDATE tblCustomers SET [FirstLastName] = [LName] & "," & [FName]

This is not a robust solution, though, unless you have some kind of
guarantee that you will only ever have one John Smith... which of course
there isn't! That's why people tables always involve some unique primary
key, even though it may not be meaningful. For instance, in the US, the
standard PK used is the social security number which is guarateed to exist
for every person, and be unique. In other countries when nothing similar
exists, one common workaround is to use just an autonumber field. Give it
some thought...

HTH,
Nikos

Need to combine two fields into one in a customer database table, for
example; first name field and last name field need to combine into a new
field called first/last name, for the purpose of creating a primary key
field to avoid duplicate records. Any help would be greatly appreciated!
Thomas

 

[Nikos Yannacopoulos]

Hi Peng Shun Jun,

I am afraid I do not understand your question; why are what english?

Nikos

My name is Peng Shun jun, Why are they english

Thomas,

Assuming the original table is called tblCustomers, and has two fields
called LName and FName, and you have created a new one called
FirstLastName, then you need to run an update query on it like:

UPDATE tblCustomers SET [FirstLastName] = [LName] & "," & [FName]

This is not a robust solution, though, unless you have some kind of
guarantee that you will only ever have one John Smith... which of course
there isn't! That's why people tables always involve some unique primary
key, even though it may not be meaningful. For instance, in the US, the
standard PK used is the social security number which is guarateed to exist
for every person, and be unique. In other countries when nothing similar
exists, one common workaround is to use just an autonumber field. Give it
some thought...

HTH,
Nikos

Need to combine two fields into one in a customer database table, for
example; first name field and last name field need to combine into a new
field called first/last name, for the purpose of creating a primary key
field to avoid duplicate records. Any help would be greatly appreciated!
Thomas