contacts database

[Mark]

I am a bit of a novice with Access (2002). I am creating
a contacts database that has both organisations and
individuals.
I would like to have a data entry form for individuals
that has a drop down box for organisation which populates
the individual's address, phone number etc when an
organisation is selected.
There will also be individuals who are not part of an
organisation - who will need their own discrete address
etc details.
I presume I have to creat two tables - one for
organisations and one for individuals that are linked.
Could somebody please point me in the right direction.

 

[Tim Ferguson]

I would like to have a data entry form for individuals
that has a drop down box for organisation which populates
the individual's address, phone number etc when an
organisation is selected.

Oh no you wouldn't: if it's the organisation's attributes (address, phone)
then it belongs in the organisation table. You can always display them
using a query whenever you want to see an Individual alongside its
Organisation details.

There will also be individuals who are not part of an
organisation - who will need their own discrete address
etc details.

In this case, you may have a slightly more complex requirement. You could
have one entity called ThingsIHaveToBeInContactWith, which come in two
varieties, People and Organisations. There may be a relationship(s) between
some People and some Organisations which you could build in too. Vis:

Addresses(*AddressID, FirstLine, SecondLine, City, Country,
Postcode)

People(*ThingNumber(fk), FName, LName,
WorksFor(fk references Organisations), etc...)

Organisations(*ThingNumber(fk), CompanyName,
PrincipalRep(fk references People), etc...)

Things(*ThingNumber, Address(fk), PhoneNumber, IsMostValued,
etc...)

The fields in People are those that don't appear in Organisations, etc. You
could define the relationships between them as you like: not all
Organisations have to have a PrincipalRep and not all People WorkFor an
organisation. And so on...

In some situations you may put the PhoneNumber in the Addresses table. Do
some people have phones without addresses? On the other hand, you might
want to put all the phone numbers in a table on their own, to allow for
multiple numbers like this:

PhoneNums(*NumToDial(this is text, of course), BelongsTo(fk into Things),
Type(fixed, mobile, fax, etc)...)


Hope that helps



Tim F

 

[Lynn Trapp]

Tim,
How about this approach? Create a Contacts table that has a Contact Type
field in it. The Contact Type field can be populated with either "P" (for
person) or "O" ( for Organization). Then have a ContactPersons table that
has a foreign key to the Contacts table. Create a data entry screen that is
a form/subform and that doesn't show the subform, for creating child
records, unless the Contact type is "O".

Just a passing thought.

 

[Mark]

Thanks Tim,
Sounds a bit more complicated than I thought, but I feel
that this approach is what we need. However could you
clarify some parts please: (I'm a bit green)

There may be a relationship(s) between
some People and some Organisations which you could build in too. Vis:
Addresses(*AddressID, FirstLine, SecondLine, City, Country, Postcode)
People(*ThingNumber(fk), FName, LName,
WorksFor(fk references Organisations), etc...)

HOW DO I BUILD THE WORKSFOR RELATIONSHIP?

Organisations(*ThingNumber(fk), CompanyName,
PrincipalRep(fk references People), etc...) AS ABOVE FOR PRINCIPALREP
Things(*ThingNumber, Address(fk), PhoneNumber, IsMostValued,
etc...)

HOW DO I BUILD THESE RELATIONSHIPS?

In some situations you may put the PhoneNumber in the Addresses table. Do
some people have phones without addresses? On the other hand, you might
want to put all the phone numbers in a table on their own, to allow for
multiple numbers like this:
PhoneNums(*NumToDial(this is text, of course), BelongsTo (fk into Things),
Type(fixed, mobile, fax, etc)...)

FOR THIS DB THERE ONLY NEEDS TO BE A SWITCHBOARD # AND
FAX # FOR ORGANISATIONS, AND MOBILE, HOME AND ALT/FAX FOR
INDIVIDUALSAlso the individuals and organisations fall into common
categories (such as supplier, meeting attendee, etc.). Do
I build a Category table with a fk to individuals and a
fk to organisations - will that work?
Cheers,
Mark.>

 

[Mark]

Oh no you wouldn't: if it's the organisation's
attributes (address, phone)

then it belongs in the organisation table. You can always display them
using a query whenever you want to see an Individual alongside its
Organisation details.

HOW DO I DO THAT?

 

[Tim Ferguson]

However could you
clarify some parts please: (I'm a bit green)


HOW DO I BUILD THE WORKSFOR RELATIONSHIP?

It's a field that matches the Organisations.ThingNumber field -- presumably
a Long Integer (see below). Make sure it is set to Required=No and
DefaultValue=Null (access does not default to this, so you have to do it
yourself). Then use the Relationships window, add the two tables, and drag
the Organisations.ThingNumber field over to the People.WorksFor field (or
vice versa) and answer the following questions.

AS ABOVE FOR PRINCIPALREP

As above.

HOW DO I BUILD THESE RELATIONSHIPS?

If ThingNumber is an autonumber, then the corresponding
Organisations.ThingNumber and People.ThingNumber fields need to be Long
Integer, because autonumber is a special kind of long. You can add all
three tables to the Relationships window and drag Things.ThingNumber onto
Organisations.ThingNumber (this time the direction does matter!) and create
the 1:1 relationship. Ditto for the People table.

for this db there only needs to be a switchboard # and
fax # for organisations, and mobile, home and alt/fax for
individuals

I would probably go for a proper 1:many for all of them, for the sake of
simplicity. Your UI can try to limit the appearance of a Home phone for an
Organisation, since it is probably not a fatal error. Make it refer to the
Things table not the subtype tables, though.

Phones(*NumberAsTextField (long enough to handle the area code etc),
ThingNumber(fk references Things), TypeCode (mob, fax, hom, etc))

Also the individuals and organisations fall into common
categories (such as supplier, meeting attendee, etc.). Do
I build a Category table with a fk to individuals and a
fk to organisations - will that work?

BelongsTo(*CategoryCode (text), *ThingNumber(fk references Things))

If you need very close control over the categories, you need another table
of the categories themselves vis

Categories(*CategoryCode, Description, etc.)

and then make BelongsTo.CategoryCode a fk that references the new table.
This now makes a standard many:many relationship.

<g> You thought you could do this all with one table didn't you? Welcome to
the wonderful world of relational design! This I think is the overall state
of play:


Organisations --------+- Things -+---+----< Phones
| | |
People ---------------+ | +----< Addresses
|
+-< BelongsTo >-- Categories


The right-hand side is pretty standard one-to-many and many-to-many stuff.
The interesting part is the relationships on the left. One-to-one
relationships are not very common, but this scenario is called subtyping
and is one of the few legitimate reasons for them.

Sorry: that is a bit long, but I hope it will explain some of the black
magic at the top.

All the best


Tim F