Contact database - linking tables

[kleivakat]

I'm trying to create a contact database. This seems so basic, so thanks for
your patience. I'm using a template for contacts which seemed to be working
fine, but I found myself entering the same company information (company name,
address, steet, vendor number, web page, etc.) for each contact at that
company. So I thought perhaps it would be easier to have separate tables,
one for the company info, and a table for names and link the two. So on my
names table, I have field for first and last name, title, company name,
direct phone line, e-mail address. Then I tried linking the two tables where
"company name" in the company table is identified as the same as "company
name" in the names table.

I get an error message when I try to do that reading "No unique index found
for the referenced field of the primary table." I'm not sure what that
means. One other question, the two fields that contain company name
information, should they have different and unique field names in each table,
or should they have the same name?

Thanks for letting me know the easiest way to do this.

KK

 

[Ken Sheridan]

When you create a one-to-many relationship like this the referencing table
(Contacts) contains a foreign key column (Company Name) which references the
primary key column (Company Name) of the referenced table (Companies). This
is fine provided all company names are unique; its what is called a 'natural
key'. The field names in each table don't have to be the same, but
semantically its better if they are. So, assuming that to be the case you
simply have to designate the Company name field in the Companies table as its
primary key in design view. If you already have another field, such as an
autonumber CompanyID designated as the primary key you can delete that field
from the table.

If on the other hand two companies could have the same name, then you would
need a 'surrogate key' field as the primary key of Companies, such as an
autonumber CompanyID. In this case you would also have a CompanyID field in
the Contacts table, but not an autonumber in this case, just a
straightforward long integer number data type.

From what you say it does sound like the company names are unique, so you
can use it as a natural key. By designating it as the primary key of
Companies it will automatically be uniquely indexed. The corresponding
Company Name field in Contacts should be indexed non-uniquely (duplicates
allowed) as its value will be repeated for each contact per company.

For data entry in a Contacts form you'd normally use a combo box bound to
the Company Name field, with a RowSource property of:

SELECT [Company Name] FROM [Companies] ORDER BY [Company Name];

You can then simply select a company from the combo box's drop-down list.

Another possible interface would be to have a Companies form, in single form
view, and within it a Contacts subform in continuous form or datasheet view,
linking the parent form and subform on Company Name. To add contacts for a
company would then simply be a question of inserting rows in the subform.

When using natural keys like this you should also enforce cascade updates
when creating the relationship between the tables. That ensures that if a
company name is changed in the Companies table, the name of that company will
be automatically changed in any matching rows in the Contacts table. If you
want all matching rows in the Contacts table to be automatically deleted if a
row is deleted from the Companies table then enforce cascade deletes also.
If you don't do this you'll be unable to delete a company until all its
contacts have been deleted. It for you to judge which of these strategies is
appropriate in your case.

If you decide that company names might not necessarily always be unique,
even if they are now, and therefore use surrogate numeric keys, you'd set up
a combo box for selecting a company in a contacts form rather differently, as
follows:

RowSource: SELECT [CompanyID], [Company Name] FROM [Companies] ORDER BY
[Company Name];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England