Concatinate data from seperate table

[ntonline]

I am playing around with Access 2003 and am in the process of creating forms right now. I am running into issues with the following forms/tables:

Contact (ContactID, FullNameID, Title, Company, PhoneID, AddressID, EmailID, Webpage, Notes)



FullName (FullNameID, PrefixID, First, Middle, Last, SuffixID)

I have already created the tables and the forms for each item. I have set up the forms so that when a user is in the Contact Form and clicks on the FullNameID attribute, the FullName form opens.

What I would like to have happen now is after the FullName form is filled out and closed, the FullNameID attribute in the contact form populates (or concatinates) the information from the FullName form.

If you know how make this happen, please help me out.

P.S. I got the idea from the MS Outlook contact form. I'm simply experimenting and trying to duplicate the functionality to get a better understanding of Access and databases overall.

[TheShabz]

Why not combine them into one table?
tblContact
ContactID
Prefix
FirstName
MiddleName
LastName
Suffix
Title
company
etc...

Full name can be concatenated on the fly wherever you need it to be. You're making things more difficult than need be.

[jzwp11]

As you are just starting out with Access, you have to understand the rules of normalization which govern how you set up your tables. This site provides an overview of normalization. You might also check out this site for some good tutorials for someone just starting out.

Just as an example, if a company has many contacts, a typical table structure would look like this:

tblCompany
-pkCompanyID primary key, autonumber
-txtCompanyName

tblCompanyContacts
-pkCompanyContactsID primary key autonumber
-fkCompanyID foreign key to tblCompany
-txtFName
-txtLName

Now, if a contact has many contact methods (a home phone #, mobile phone #, work phone #, fax #, e-mail address etc.) that describes a one(contact)-to-many(contact methods) relationship

tblContactMethods
-pkContactMethodID primary key, autonumber
-fkCompanyContactID foreign key to tblCompanyContacts
-fkContactMethodTypeID foreign key to tblContactMethodTypes
-txtContactInfo (the actual e-mail address or phone number)

tblContactMethodTypes
-pkContactMethodTypeID primary key, autonumber
-txtContactMethodTypeName

The above table will hold records for names such as home phone, mobile phone etc.