Link Data

[David F-B]

Hi All

Sorry if this a blindingly obvious question to most people, but ...

I have a single table database of contacts. Some contacts are members
of my club, some are not. Contacts are employees of different
companies. They often move about a lot.

At present, when a person moves from one company to another, I get the
names and addresses of the companies. Then I write them down on a
piece of paper. Then I retype the mover's details with the new
company address. This leads to errors when I mis-type things. I'm
wondering if it is ok to have company and people details in the same
table, even though they are employees of that company.

I suppose ideally I want a system that allows me to label the
companies X, Y Z. When someone moves I want to be able to put into a
box that he has moved from X to Y. Kind of like having an arrow
pointing from one set of details and moving it so it points to another
set of details.

I suppose I need to break up the table and put the people in one table
and the company details (address, phone numbers, etc) in another? If
I stick an autonumber field in each table, I would get unqie numbers
for each person and a unique number for each company (after I spend
time filtering out all the duplicated company entries in the existing
database table). After that, it becomes a matter of linking the two
tables together somehow to show that person number such and such has
moved from company 01 to company 02. The actual company details such
as address will be in the company table and should not need altering.

I suppose I just want to have a company field in the person table and
alter the value for that field.

At present I'm just thinking about using autogenerated numbers. Is
there any process for autogenerating letters or words as a reference
code?

It might be easiest if I have the list of companies in the company
table as a list box in a form showing the person's details. Is there
a way of creating a list box which both shows a list of the entries in
the company table (ie the companies) while also allowing me to add new
companies if need be?

I have looked at the Northwind example database, but it doesn't seem
terribly appropriate to my situation (making a contacts database) or
my level of compentence (or complete absence thereof).

Profound thanks for any light that can be shone into the gloom of my
ignorance.

David F-B

 

[Van T. Dinh]

Perfect!

You are moving in the RIGHT direction.

You have 2 *distinct* entities here: Persons & Companies.

Each entity should be stored in a separate Table. Then you can link the 2
Tables using either One-to-Many relationship or Many-to-Many relationship.
Which one to use depends on your requirements. For example, if you only
want to link a Person to *only* his current Company (and assuming that even
if the Person has many concurrent jobs with different Companies, you only
want to show one of the current Companies, perhaps the "main"one), then you
can use One (Company)-to-Many (Persons) relationship. OTOH, if you want to
be able to store more than one current employment or you want to store the
history of employment, you will need the Many (Company)-to-Many (Persons)
relationship.

Check Access Help on the One-to-Many and Many-to-Many relationship.

OTOH, reading a book on Relational Database Design Theory will probably make
you proud of yourself for your logical thinking and give you much more info.
than Access Help can provide (after all, Access is supposed to be used
*after* the Database / Table Structure has been thought out).

--
HTH
Van T. Dinh
MVP (Access)

 

[David F-B]

Thanks for the encouragement. I have not had a chance to pick up a
book from the library as yet. But I thought I'd have a stab at
setting things up and see how far I got. I can report a sort of
success.

I took the existing database/table and worked out which fields I
wanted where. I then set up a query which would pick up all the
companies and copy them to a new table which, being terribly original,
I called Companies.

I then closed the original table and copied it to the same database,
calling the new table People. I then went through the fields and
deleted the fields I did not want in People.

So, I now had all the fields where I wanted them. More importantly I
also had the data.

The examples show autogenerated ID codes being used to keep track of
people. I thought that I would have to manually input a company code
into the field in People. Trouble is I have 2,000 contacts!

Late at night I realsied I already had the reference I needed: The
individual company name. As they were no longer duplicated in
Company, I thought they would make an excellent ID. I remade the
People table, this time leaving the company name field in.

So, I have one table with people's names, details and company and
another with company names and addresses.

I linked the two tables together and then produced a query which used
elements from both tables. I now have all the people on the original
database listed by company, with their company details following.

This is where I have hit two snags, which I would appreciate some
guidance with.

Firstly, I have some entries on the People table who do not have any
corrsponding company. Can I pick them up with this query too?

Secondly, although the query lists people and their companies, I
cannot find a way of editing this information to change people's
companies (the object of the exercise). I did an autoform based on
the query as some queries only work through forms. However, no luck.

Guidance would be much appreciated.

Many thanks
David F-B

 

[Van T. Dinh]

From your description, I assumed that you decided to go with the One
(Company)-to-Many (Persons) relationship rather than Many-to-Many.

In this case, you need a "linking" Field in the Table Persons to point to
the related Record in the Table Companies. Let say the *PrimaryKey* Field
in Table Companies is "CompanyID", then you need a Field "CompanyID" in the
Table Persons that store the related CompanyID. This Field is called
ForeignKey since it comes from another Table. Personally, I tend to use
"frg_CompanyID" to indicate it is a ForeignKey and to distinguish it from
the Field "CompanyID" in the Table Companies.

You can, of course, use the Field [Company Name] in the 2 Tables as
PrimaryKey / ForeignKey but I tend to use surrogate key (e.g. your
AutoNumber Field) as PrimaryKey since there may be spelling errors in using
[Company Name].

For your question:

Q2: You need to join the 2 Tables using PrimaryKey / ForeignKey to make
your Query updateable. If you linked the 2 Tables by [Company Name] and
[Company Name] is not the PK in the Table Companies, your Query is NOT
updateable and you cannot modify the data.

Q1: Persons not related to Companies won't have value for [Company Name] so
you can use something like:

SELECT Persons.*
FROM Persons
WHERE Persons.[CompanyName] Is Null

Suggest you check out Relational Database Design Theory which explains more
about Primary Key / Foreign Key.