newbie-joining fileds

[FT OFFICE]

Can anyone tell me how I can take the info that I have entered into multiple
fields and join them into one field?

For instance, I have a table with employees' first, middle and last name. As
well as social security # and office code. (which office they work in)

I want to do two things: I want to have one field with the employee's last
name followed by ,first name (without having to enter it as such, obviously)

And, I want to be able to assign their employee number by their office code
and social security last 4.

So if I enter first name John and last name Smith, 123-45-6789, (office
code) FT, I will wind up with 2 new fields that look like FT6789 and Smith,
John.

Any help would be greatly appreciated!

-Rob

 

[Dirk Goldgar]

Can anyone tell me how I can take the info that I have entered into
multiple fields and join them into one field?

For instance, I have a table with employees' first, middle and last
name. As well as social security # and office code. (which office
they work in)

I want to do two things: I want to have one field with the employee's
last name followed by ,first name (without having to enter it as
such, obviously)

And, I want to be able to assign their employee number by their
office code and social security last 4.

So if I enter first name John and last name Smith, 123-45-6789,
(office code) FT, I will wind up with 2 new fields that look like
FT6789 and Smith, John.

Any help would be greatly appreciated!

Note: you do *not* want to have a field in your table for the
employee's full name, when you've also got the various parts of the
name. Instead, you want to define the full name as a calculated field
in a query; that is, the FullName field is defined in the query as the
result of an expression. Here's an expression you might put in the
query designer to define such a field:

FullName: [LastName] & ", " & [FirstName] & (" "+[MiddleName])

Note that the above was entered all on one line, though it may have been
wrapped to two lines by the newsreader.

The trick with (" "+[MiddleName]) is designed to handle the case where
[MiddleName] is Null -- the use of the '+' operator ensures that there
will not be a blank space on the end of the name if that is the case,
because anything+Null yields Null.

The situation with the computed employee number is a bit different. I
assume you're going to be sorting, searching, and querying on this
field, so you're going to want to create a real, permanent field in your
table for it. You'll have to deal with the issues of what happens if
the employee changes offices, or if the employee's SSN turns out to have
been entered incorrectly and must be corrected after the employee number
has been assigned. Would you change the employee number in such a case?
I certainly wouldn't use such a field as the primary key of my Employees
table, because then changing it would invalidate the foreign key in
related tables (unless you use Cascade Updates in the relationship
definitions).

Anyway, to calculate this field you'd need code in the form you use to
do employee maintenance. You'll need to use a line of code like this:

Me.EmployeeNumber = Me.OfficeCode & Right(Me.SSN, 4)

You'll probably need to execute this code from two different events:
the AfterUpdate event of the EmployeeNumber control, and the AfterUpdate
event of the SSN control. It's up to you whether you want to include to
forbid, or allow with a warning, the changing of an already assigned
EmployeeNumber.

 

[John Vinson]

Can anyone tell me how I can take the info that I have entered into multiple
fields and join them into one field?

For instance, I have a table with employees' first, middle and last name. As
well as social security # and office code. (which office they work in)

I want to do two things: I want to have one field with the employee's last
name followed by ,first name (without having to enter it as such, obviously)

And, I want to be able to assign their employee number by their office code
and social security last 4.

So if I enter first name John and last name Smith, 123-45-6789, (office
code) FT, I will wind up with 2 new fields that look like FT6789 and Smith,
John.

These concatenated fields SHOULD NOT EXIST in your Table. Period.
They're redundant; they waste space; and if Janet Smith marries Robert
Fitzgerald and changes her name, you would have two places to fix the
name.

Instead, create a Query based on your table; in two vacant Field cells
type

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

and

BuiltID: [OfficeCode] & Right([SSN], 4)

Note that if you have two employees in the same office they may very
well have the same last four digits to their SSN; this code cannot be
expected to be unique.

The Query fields can be used for searching, for sorting, and as the
control source of textboxes on forms or reports; they cannot be (and
of course should not be) edited since they depend on other fields.