combining two or more table fields into a single table field

[sasquatchlgmt]

I am using access 2007
I am creating a data base that associates a record in the Main Table with
individuals by their function.
The Main Table contains separate fields for all of the possible functions.
The Main Table function fields allow multiple individuals, from the list of
all possible individuals that can perform the specific function associated
with that field, to be associated with an individual record.
There is a table that identifies individuals and the function that they can
perform. This table contains and id field (autonumber) a combined name
(CombinedName) field, a first name (FirstName) field, a last name LastName)
field and T/F fields for all possible functions. An individual may have more
than one function in relationship to the Main Table record.
The input form for this table allows the user to input the first name, last
name and indicate the functions that the individual can perform. The combined
name field as well as the id field are also on the form but are not visible.
On the form I can combine the name fields with an expression = LastName & “;
“ & FirstName into the CombinedName field. Part of my problem (Prob 1) is
that it does not write this field to the table.
The input form for the Main Table has field locations for all possible
functions that may need to be associated with the Main Table record. I use a
query to select the records of the individuals who can perform that
associated function. I will be saving the CombinedName field in the Main
Table record. This is done so that the full name of the individual will be
displayed in either the form or a report as there common names like Smith,
Jones, Martin etc where there may be more than one with the same last name
associated with the Main Table record.
I also need a procedure that will automatically create the combined name as
a result of a modification of either the first or last names.
I also want to be able to use this procedure to redo all combined names in
the table before running report to insure that all combined names are correct.
Any sugguestions!

 

[Arvin Meyer [MVP]]

You never should or need to store data which can be decomposed. Never store
first name and last name together. That's the first rule of database
relations also known as 1NF (First Normal Form) You can always concatenate
the 2 fields in an expression displayable in a query, form, report, or any
other output.

Please read an elementary book on relational databases to find out why. One
I can suggest is:

Database Design for Mere Mortals by Michael J. Hernandez
http://www.amazon.com/Database-Desi...=sr_1_4?ie=UTF8&s=books&qid=1235105771&sr=8-4

or read the excellent tutorial by MVP Crystal Long

http://www.accessmvp.com/Strive4Peace/Index.htm