Two questions: Multiple people at one office; multiple contacts for one person

[Matthew A. Murray]

Hi... I just joined the newsgroup. I'm helping a friend out with a project
in Access 2007 that I'm having some problems with... He asked me because he
knows I had experience with databases in the past, though I haven't used
Access since the late 1990s and most of my experience was never in Access in
the first place! So I understand a lot of database basics, but how to
translate that to Access is what I'm having a problem with. Hence these two
questions!

First, I have a main form on which the user can enter information about
people--address, e-mail address, phone numbers, hair color, eye color,
things like that--via two tabs on the form. (Each of the tabs currently
contains a subform about one specific type of information.) On the third
tab, I want to add a business contact section for each person, with an
office name, phone number, fax number, and website through which they can be
reached. Since there is a very strong possibility multiple people might be
contacted through the same office, I set up an office table and office form
so that information can be entered. So far so good.

But I don't know how to link the information in the office table to the
specific people who can be contacted through that office. I created a field
in the Person table for the office contact number, which relates directly to
the AutoNumber field in the Office database, but I'm not sure what else to
do. Ideally, on the office contact tab in the main (person) form, the user
would select from a list of offices, and then the contact information for
that office would appear there. (I don't think the user should be able to
update the office information from the person form.) However, because
people can change offices fairly easy, I want this information to be easy to
update as well--the people I'm helping out with this know nothing about
databases and want all the information to be entered and accessed in as
friendly and non-techincal a way as possible.

Second: The user also needs to be able to specify that each person is
connected with a specific project, about which they met on a certain date.
However, it needs to be possible for there to be multiple projects per
person. I was thinking this information could also be entered on the main
person form, in yet another tab, but I really am kind of stymied about the
best way to go about this.

I poked around in some of the sample templates that Microsoft provides with
Access 2007, and found one that closely mimics the functionality I want: In
"Students," if you go to the Guardian Information tab, as soon as you've
entered the information for one guardian, a brand new series of fields
appears below it to allow you to enter a second guardian, and so on. That's
almost exactly what I want to do here, albeit with different tables and
fields. Unfortunately, I've found that poking around "behind the scenes" in
the various sample bases doesn't automatically make it easy to understand
how everything works, and I haven't been able to figure this out, either.

If anyone could provide me some assistance with these issues, I would be
extremely appreciative. Thanks!


Sincerely,

Matthew Murray

 

[Larry Linson]

First, I have a main form on which the user can enter
information about people-- <snip> --via two tabs on
the form. (Each of the tabs currently contains a subform
about one specific type of information.) On the third
tab, I want to add a business contact section for each
person, with an office name, phone number, fax number,
and website through which they can be reached. Since
there is a very strong possibility multiple people might be
contacted through the same office, I set up an office table
and office form so that information can be entered.
So far so good.

But I don't know how to link the information in the office table to the
specific people who can be contacted through that office. I created a
field in the Person table for the office contact number, which relates
directly to
the AutoNumber field in the Office database, but I'm not sure what else to
do. Ideally, on the office contact tab in the main (person) form, the
user would select from a list of offices, and then the contact information
for
that office would appear there. (I don't think the user should be able to
update the office information from the person form.) However, because
people can change offices fairly easy, I want this information to be easy
to update as well--the people I'm helping out with this know nothing about
databases and want all the information to be entered and accessed in as
friendly and non-techincal a way as possible.

A Combo Box, bound to the Office ID autonumber, but displaying the Office
name or other identifying information should work well to allow easily
changing the Office.

Second: The user also needs to be able to specify that each person is
connected with a specific project, about which they met on a certain date.
However, it needs to be possible for there to be multiple projects per
person. I was thinking this information could also be entered on the main
person form, in yet another tab, but I really am kind of stymied about the
best way to go about this.

If only one person may be connected to a particular project, then you need a
"foreign key" in the Projects table to refer to the Person ID in the Person
table. If you use a Form embedded in a Subform Control to show/add
Projects, and use the Link Master Fields and Link Child Fields properties of
the Subform Control, that should set the value automagically.

However, if more than one person may be connected to a project, as well as
multiple projects to a person, then you have a many-to-many relationship
which is handled in Access by creating a "junction" or "intersection" table
with foreign keys to both People and Projects. Basic the Form in the
Subform Control on a Query, joining the Junction Table to the Projects
table, and including the Foreign Key to the People table (to use as the Link
Child Fields) and also the Primary Key of the Project Table (an Autonumber,
I'd guess) should be a start on handling that arrangement.

Larry Linson
Microsoft Access MVP

 

[pietlinden]

Hi... I just joined the newsgroup. I'm helping a friend out with a project
in Access 2007 that I'm having some problems with... He asked me because he
knows I had experience with databases in the past, though I haven't used
Access since the late 1990s and most of my experience was never in Access in
the first place! So I understand a lot of database basics, but how to
translate that to Access is what I'm having a problem with. Hence these two
questions!

First, I have a main form on which the user can enter information about
people--address, e-mail address, phone numbers, hair color, eye color,
things like that--via two tabs on the form. (Each of the tabs currently
contains a subform about one specific type of information.) On the third
tab, I want to add a business contact section for each person, with an
office name, phone number, fax number, and website through which they can be
reached. Since there is a very strong possibility multiple people might be
contacted through the same office, I set up an office table and office form
so that information can be entered. So far so good.

But I don't know how to link the information in the office table to the
specific people who can be contacted through that office. I created a field
in the Person table for the office contact number, which relates directly to
the AutoNumber field in the Office database, but I'm not sure what else to
do. Ideally, on the office contact tab in the main (person) form, the user
would select from a list of offices, and then the contact information for
that office would appear there. (I don't think the user should be able to
update the office information from the person form.) However, because
people can change offices fairly easy, I want this information to be easy to
update as well--the people I'm helping out with this know nothing about
databases and want all the information to be entered and accessed in as
friendly and non-techincal a way as possible.

Second: The user also needs to be able to specify that each person is
connected with a specific project, about which they met on a certain date.
However, it needs to be possible for there to be multiple projects per
person. I was thinking this information could also be entered on the main
person form, in yet another tab, but I really am kind of stymied about the
best way to go about this.

I poked around in some of the sample templates that Microsoft provides with
Access 2007, and found one that closely mimics the functionality I want: In
"Students," if you go to the Guardian Information tab, as soon as you've
entered the information for one guardian, a brand new series of fields
appears below it to allow you to enter a second guardian, and so on. That's
almost exactly what I want to do here, albeit with different tables and
fields. Unfortunately, I've found that poking around "behind the scenes" in
the various sample bases doesn't automatically make it easy to understand
how everything works, and I haven't been able to figure this out, either.

If anyone could provide me some assistance with these issues, I would be
extremely appreciative. Thanks!

Sincerely,

Matthew Murray

Does a schema like this work for you?
Office---(1,M)---Person---(1,M)----WorksOn---(M,1)---Project---
(1,M)----Client

Each Office is staffed by several People. Each Person works at one
Office.
Each Person can work on one or more projects. Each project can we
worked on by several people.

Each Project is funded by/sponsored by a single Client.
Each Client can sponsor many Projects.

Office(OfficeID (PK), PhoneNo...)
Person(PersonID (PK), WorksAtOfficeID...)
WorksOn(PersonID (PK1), ProjectID (FK2), Hours)
Project(ProjectID (PK), Description...)

PK=Primary/Unique key.
FK=Foreign Key (joins the tables together)