locked
Acc2016 - Many Contacts in one Company: better in two tables? How to set up the form? [New user] RRS feed

  • Question

  • I created a database with a few functions, one is for our sales employees to enter Contact data and update Sales Status and add notes.

    The problem is with the Contacts table. It can include five or ten contacts at one company. One company has about 6 columns of data tied to it. All of this is currently stored within the Contacts table.

    I have a good idea of how to split this company data into a 'tblCompanies' but my problem is with the subforms and everything that comes after. So...

    1. Is this necessary? The templates seem to put all of this data in one table (but I know that's wrong because it's a one-to-many.)
    2. How do I create a form that lets a user enter a new Contact and a new Company?
    3. How do I create a form that lets a user enter a new contact at an existing Company?

    Ideally, I would like the user to be able to type the company name and AutoFill will suggest an address and post code.

    Thank you for reading. All advice/suggestions/questions/critiques are appreciated!

    Wednesday, June 29, 2016 9:32 AM

Answers

  • It's a good idea to create a separate tblCompanies, since otherwise, you'd be entering the company address for each of the company's contacts. That is redundant and error-prone.

    Let's say CompanyID is the Primary Key of the new table. An AutoNumber field is ideal for this.

    Once you have created tblCompanies, create a CompanyID field (Number, Long Integer), and create a relationship between tblCompanies and tblContacts on CompanyID, with Enforce Referential Integrity.

    Since there is a one-to-many relationship from companies to contacts, I would create a main form based on tblCompanies and a subform based on tblContacts. Access will automatically join them on CompanyID.

    If you want to add a contact to an existing company, locate that company in the main form, then create a new record in the subform.

    If you want to add a contact to a new company, first create a new company record in the main form, then create a new contact record in the subform.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 29, 2016 11:56 AM

All replies

  • It's a good idea to create a separate tblCompanies, since otherwise, you'd be entering the company address for each of the company's contacts. That is redundant and error-prone.

    Let's say CompanyID is the Primary Key of the new table. An AutoNumber field is ideal for this.

    Once you have created tblCompanies, create a CompanyID field (Number, Long Integer), and create a relationship between tblCompanies and tblContacts on CompanyID, with Enforce Referential Integrity.

    Since there is a one-to-many relationship from companies to contacts, I would create a main form based on tblCompanies and a subform based on tblContacts. Access will automatically join them on CompanyID.

    If you want to add a contact to an existing company, locate that company in the main form, then create a new record in the subform.

    If you want to add a contact to a new company, first create a new company record in the main form, then create a new contact record in the subform.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 29, 2016 11:56 AM

  • Since there is a one-to-many relationship from companies to contacts, I would create a main form based on tblCompanies and a subform based on tblContacts. Access will automatically join them on CompanyID.

    Thanks for the explanation. This is exactly where I've been stuck from the beginning. I've been basing the forms off of the Contact data rather than the Company data.

    Very helpful, much appreciated.

    Thursday, June 30, 2016 8:45 AM