Form/table interaction.

[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

 

[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

You might want to have a look at Database Design for Mere Mortals by
Michael Hernandez. He breaks down database design in plain English.
And a good design is critical to a useful/functional database.

 

[Allen Browne]

Matthew, consider what kinds of relationships you need between your tables
first. Then design the interface to match the relationships.

One "office" can have many "people." Do you also need to track that one
person can be associated with multiple offices - either simultaneously
(part-timers) or historically (remembering who worked for whom in the past)?
If so, you have a many-to-many relation, and so you will need a 3rd table
with fields like this:
OfficeID relates to Office.OfficeID
PersonID relates to Person.PersonID

For the interface, you don't need a subform for the first 2 tabs (the people
fields.) Just place them directly on the pages of the main form. Then do use
a subform on the 3rd tab, and bind it to the junction table. The subform
will show in continuous form view, and will have a combo for choosing the
offices for this person.

Projects are almost certainly a many-to-many relation as well (i.e. one
project can have many people, and one person can be in many projects.) You
will therefore need the junction table with fields ProjectID and PersonID.
And the 4th tab of your Person form can have a subform bound to this
junction table.

Having sorted all that out, you may wish to look at an alternative structure
for handling the people and offices. Since both companies and persons have
similar related data (each other, projecfts, addresses, phone numbers, ...),
and you may need to search on each, there could be a case for putting the
companies and persons into one table (clients), and then defining the
relations between clients. Ignore that if you find it confusing. But if it
sounds interesting, see:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html