Numerous companies and many contacts

[mtobey]

Hello,

I want to create a database with 132 companies and each company has numerous 'contacts'.

Have made several stabs at it but don't understand how to get the company name that each contact belongs to, to show up in the contact table.

I'm attempting to import from an excel spreadsheet to avoid entering the information manually.

Would love a simple explanation to this seemingly complex and frustrating issue!:banghead: ...over my head anyway...

thanks much!
Melinda

 

[John Big Booty]

What you will need is at least two tables;

TBL_Company
CompanyID (PK)
CompName
CompAdd

TBL_Contacts
ContactID (PK)
CompanyID (FK)
ContName

This is the absolute bare minimum, in reality you will need numerous other table to maintain your normalised structure.

You will need a Main Form that is bound to your table TBL_Company and a Sub-Form that is bound to the table TBL_Contacts. Your main form and sub form will be link by the field CompanyID which forms the Primary Key (PK) of your table TBL_Company and Foreign Key (FK) of your table TBL_Contacts.

 

[Pat Hartman]

As you can see by John's example, company name is in the company table NOT in the contacts table. The tables are "connected" by using the company ID which in this case is probably an autonumber. To turn a normalized schema back into information, you use queries that join the tables.

To get from the spreadsheet to the two properly designed tables also requires the use of queries. To create the company table, you would create a make-table query that selects the company name and any other company-only data such as address and web page, etc. Then to create the contacts data, create another make-table query that selects the contact data and joins to the newly created company table on enough of the company fields to get a unique match. This query then selects the CompanyID from the company table and uses it to populate the CompanyID in the contacts table.

 

[mtobey]

John & Pat - thanks for the input!

I'm sure there's something simple I'm missing here...

Understand the 2 tables and PK for company_tbl, (which will be their account number) and that company_id will be FK in contact_tbl

In short, the excel sheet is set up like this:
Company/address(es)/contact(s)/account number
1 company, 1 account number, address(es)(some companies have several locations) and most all have more than 1 contact.

What I don't understand is what is required for the contact designation (contact_ID) so that when I query them, they show up with their own company info or vice versa...query the company and show a list of the contacts belonging to that company...if that makes sense ...or if its even doable?

thanks,
Melinda

 

[Simon_MT]

The principle is the same as above.

Table Companies
CompanyID Primary Key
Account_Number

Table Contacts
ContactID
CompanyID
Account_Number
Contact Details etc

Parent Form based upon Company Table
Child Form based upon Contacts linking CompanyID from the two tables.

You may need to create the Contacts Table first and then create the Company table by aggregating (unique) CompanyID and the Account_Number. Then drop the Account_Number from the Contacts table.

Simon

 

[mtobey]

The principle is the same as above.

Table Companies
CompanyID Primary Key
Account_Number

Table Contacts
ContactID
CompanyID
Account_Number
Contact Details etc

Parent Form based upon Company Table
Child Form based upon Contacts linking CompanyID from the two tables.

You may need to create the Contacts Table first and then create the Company table by aggregating (unique) CompanyID and the Account_Number. Then drop the Account_Number from the Contacts table.

Simon

Simon...thanks for the reply...think it's starting to sink in now.
Could you please expound a bit more on your last sentence 'aggregating' CompanyID? ... such a newbie

 

[Simon_MT]

Import all your information into Contacts.

You will then need to create the Companies Table but there will be duplication so you need to identify unique CompanyID records and populate the Companies Table along with the Account_Number.

Simon

 

[mtobey]

Simon,

Ok I've made the contacts table...and made several queries. It seems uneccessary to create a Companies table as I can acquire all information from the Contacts table...unless I'm still missing something...

Melinda

 

[Pat Hartman]

Please go back and reread post #3 where I explain how to build the two tables. If you leave the company information in the contact table, you would need to change all the contact records if you needed to change company information. They don't do it often but sometimes companies move. My client just did it last month.

 

[mtobey]

Hi Pat - I see your point...will recreate the tables per John's diagram but still not getting how the 2 tables coincide ...I need to split the excel spreadsheet into company and contacts and import each?
Do I let Access assign primary key for each?

(For the 132 companies and over 2000 contacts, the spreadsheet lists each company name for as many contacts that are associated with that company)
In other words, there are over 2000 rows of information total, with company name and account number repeating x number of contacts
Seems like a lot of duplicated entries...

I'm gonna get this if it kills me!

thanks,
Melinda