Multiple contacts for one company

  • Thread starter Thread starter twrktec
  • Start date Start date
T

twrktec

I am just starting out with Access 2007. I am creating a contact database.
Several companies that we deal with have multiple contacts. Is it easier to
list the same company several times with each contact or is there an easier
solution to having one company listing with several contacts?
 
twrktec,

It is correct to have one Company per multiple Contacts...

tblCompanyProfile
cpCompanyProfileID (PK-Autonumber)
cpCompanyName
cpAddress1
cpAddress2
etc...

tblCompanyContacts
ccCompanyContactID (PK)
ccCompanyProfileID (FK)
ccFirstName
ccLastName
etc...

Perhaps you should take a look at...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


http://www.databasedev.co.uk/table-of-contents.html

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
I am just starting out with Access 2007. I am creating a contact database.
Several companies that we deal with have multiple contacts. Is it easier to
list the same company several times with each contact or is there an easier
solution to having one company listing with several contacts?
Click to expand...

You're using a relational database; use it relationally!

You have a perfectly classic one to many relationship. This should be handled
by having a Company table, related one-to-many to a Contacts table, using the
CompanyID as the primary key of Companies and as a foreign key in the Contacts
table.

There is a A2007 Template for exactly this application, you may want to
download it and check it out. Here are some other good resources to get you
started:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
You need at least two tables.

Table 1: Companies

Table 2: Contacts

The Contacts table should have the primary key from the Companies table as
the foriegn key to link the records together.
 
I am just starting out with Access 2007. I am creating a contact
database. Several companies that we deal with have multiple
contacts. Is it easier to list the same company several times
with each contact or is there an easier solution to having one
company listing with several contacts?
Click to expand...
standard practice is to define the company once in one table, which has
a primary key of CoID, usuallly an autonumber, and a separate table for
the contacts that contains a copy of that CoID on each row, one row per
contact. It is a One to Many relationship, and relationships are why
Access is a Relational Database.

You then can link the tables in a query, form or report on the CoID
relation.
 
FWI:


MS does have an Access 2007 "contact management" template available for
download, but it consists of a single flat table.

The color scheme is pretty cool, though.
 
Back
Top