firstname/lastname fields into one field

  • Thread starter Thread starter Lane55
  • Start date Start date
L

Lane55

Old table has employee information. Employees are uniquely
identifed by an assigned employee number. How do I create a
single field that would have lastname,firstname? I can
create a form with last name and first name fields and
display last name,first name in an unbound text box using a
formula. Is there some way to do this in a table? Lastname
is entered firstname is entered and combined name is
created? Boss is ADAMENT that there must be a new field
with lastname, firstname. Any suggestions would be
appreciated. Thanks
 
For what reason and about what exactly is your boss
adamant? When I need a concatenated name, most of the
time I either do what you have done with a calculated text
box or I add a field to a query, with the Field (top row
in design view) containing something along the lines of
FullName: [Last]&", "&[First], depending on the
situation. Tables are for storing information. Queries
are for pulling together selected information from one or
more tables. Forms and reports are for displaying
information contained in tables and/or queries. This is
an oversimplification, but it is the general idea. If you
are storing information twice you are storing it once too
often. If you put the full name into a query your boss
can view something that looks like a table anytime he or
she wishes, but your database is not encumbered with
redundancy. There are ways of using queries to update
tables, and I think it could be done in your case, but it
is almost surely unnecessary. Someone else would have to
help with the details. I am fairly new to Access, so I
have not spent much time trying to figure out how to store
redundant information.
 
Boss is an idiot!

Storing both first and last name in the same field violates the First Normal
Form, which is the bedrock of all relational databases. 1NF says that all
fields must be atomic, that is must hold one and only one piece of
information. My full name is 2 pieces of information. What happens when
you want to print a list in firstname lastname order? Don't say it will
never happen. In my experience, things that the customer claims will never
happen invariably do!

If he wants a full name field in addition to storing them separately, you
are violating the Third Normal Form. 3NF says that the information stored
in a field can not depend on the information in any other field (except the
primary key). What happens when Sally Smith gets married and changes her
name to Smith-Jones? Either you have to create code that will update the
full name field or you will have a data anomoly. Once you have done this,
you can NEVER trust your data with absolute certainty.

The rules of normalization exist because they WORK.

HOWEVERm if you must do this foolish thing, you need an Update query.
Create your new field: FullName, then the following query.

UPDATE tblEmployees SET FullName = [lastname] & ", " & [firstname];
 
I have a table for applicants -- my primary key is an
autonumber -- I have one field for last name and one for
first name. When I do query's for reports, etc. --- I
concatenate them --- so they appear as one. Sometimes I
will do first name,last name -- sometimes
lastname,firstname --- and you can sort them by putting
whichever first and selecting sort in your query. the way
to do the concatenation in a query is: Appl:
[ApplLastName] & " " & [ApplFirstName] --- just enter
exactly that in your query. Make sure you have last name
and first name pulled down in your query.
 
Old table has employee information. Employees are uniquely
identifed by an assigned employee number. How do I create a
single field that would have lastname,firstname? I can
create a form with last name and first name fields and
display last name,first name in an unbound text box using a
formula. Is there some way to do this in a table? Lastname
is entered firstname is entered and combined name is
created? Boss is ADAMENT that there must be a new field
with lastname, firstname. Any suggestions would be
appreciated. Thanks
Click to expand...

Show the boss the output of a Query containing a field defined by

FullName: [LastName] & ", " & [FirstName]

Tell him it's a table... <g> (Your boss obviously should not be
allowed to see table datasheets, given his level of relational
database expertise).

If it means your job, just add the (redundant, unneeded, and wrong)
Fullname field to the table and use an Update query to update it to
the same expression.
 
Thanks for all the input on this question. I agree boss is
not sharpest tack in the box however, he does sign
paycheck. I will try to song and dance him a bit with query
results. If that fails I will reluctantly put redundant
field in for him.
-----Original Message-----
Old table has employee information. Employees are uniquely
identifed by an assigned employee number. How do I create a
single field that would have lastname,firstname? I can
create a form with last name and first name fields and
display last name,first name in an unbound text box using a
formula. Is there some way to do this in a table? Lastname
is entered firstname is entered and combined name is
created? Boss is ADAMENT that there must be a new field
with lastname, firstname. Any suggestions would be
appreciated. Thanks
Click to expand...

Show the boss the output of a Query containing a field defined by

FullName: [LastName] & ", " & [FirstName]

Tell him it's a table... <g> (Your boss obviously should not be
allowed to see table datasheets, given his level of relational
database expertise).

If it means your job, just add the (redundant, unneeded, and wrong)
Fullname field to the table and use an Update query to update it to
the same expression.


.
Click to expand...
 
Thanks for all the input on this question. I agree boss is
not sharpest tack in the box however, he does sign
paycheck. I will try to song and dance him a bit with query
results. If that fails I will reluctantly put redundant
field in for him.
Click to expand...

If it might help, here's my boilerplate reply about derived data: feel
free to quote.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
 
Back
Top