Results 1 to 3 of 3
  1. #1
    ntonline is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    1

    Question Concatinate data from seperate table

    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.

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    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.

  3. #3
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-12-2010, 05:03 PM
  2. Update in seperate table
    By vCallNSPF in forum Forms
    Replies: 8
    Last Post: 12-23-2009, 01:18 AM
  3. Replies: 0
    Last Post: 10-18-2009, 10:44 AM
  4. Making the launched form seperate from Access
    By MonsterMaxx in forum Access
    Replies: 1
    Last Post: 09-06-2009, 12:11 PM
  5. split a column into two seperate columns
    By nybanshee in forum Access
    Replies: 2
    Last Post: 08-14-2008, 04:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums