Beginner's contact list question

[Robert J. Bonn]

I'm trying to set up a contact list in MS Access 97. I've looked
through a reference book and the program's help screens, but the light
bulb isn't quite coming on for me. If one of you could take the time
to answer two very elementary questions, I'd appreciate it.

Suppose the contact list consists of (for example) musicians and
teachers. For every person in the list, we keep name, address, etc.
For musicians, we keep track of what instruments they play, and
whether they sing or not. For teachers, we keep track of what subject
they teach, and what grade they teach ... but we also keep track of
the school's name and address (because we might meet more than one
teacher at each school, and we don't want to input the school
information more than once.)

My two questions:

1. When entering a new contact on a form, is it possible to select
"musician or teacher," and whichever one you pick, you get the
specific questions related to each? (In other words, if you pick
musician, you're asked about instruments, band name, etc, and if you
pick teacher, you're asked about school name, grade, etc.)??

2. I understand that there will be a separate table for teachers,
which would include (for example) school name and address, but would
there also be a separate table for musicians? What would it include?

The ultimate goal is to be able to (for example) find what was the
name of that saxophone player from Ohio, or who do we know who teaches
7th-grade math. At at the same time, we might need to find anyone's
e-mail address, or do a mail-merge to everyone. I think that I can
figure out the report part, but inputting the data in a convenient way
(as described above) is the part I don't get.

Again, I know this is real basic stuff. Thanks in advance for any
tips!

[John Welch]

Hey Robert-
here are some thoughts in answer to your questions:
First, would it ever be possible that someone is both a musician and a
teacher?
If so, you might want to be able to enter both kinds of info for that
person, so a tab control with "teacher" info on one tab and 'musician' info
on the other might be a good way to go.
If they are really mutually exclusive, then you can have the form "ask" you
for the relevant info if you can do some visual basic programming. In the
"after update" event of whatever control you use to select "teacher vs
musician", you would write some code to make the appropriate controls
visible and the others invisible. You could do this in various ways: you
could prefix all the musician controls with 'mus' and then make all the
controls that start with 'mus' invisible (not really beginner's visual
basic), or you could make a subform for each set of controls and hide/show
the appropriate subform.
I'm assuming you have all the musicians and teachers in the same table, and
then are thinking of having a separate 1 to 1 table for teacher info, such
as schoolID (linked to a another table of just schools: ID, name, address,
etc.), grade, subject. For the musician info, which looks like it's just
instrument and singing at this point, you could either make a separate
table, or just put these fields in the 'people' table and only fill them in
when the person is a musician. You could also keep teacher fields in the
people table as well, and set up the interface so that they can only be
filled in for teachers, since we are only talking about 3 or so fields.
hope this helps
-John

"Robert J. Bonn" <rj****@snet.netwrote in message
news:91********************************@4ax.com...

I'm trying to set up a contact list in MS Access 97. I've looked
through a reference book and the program's help screens, but the light
bulb isn't quite coming on for me. If one of you could take the time
to answer two very elementary questions, I'd appreciate it.

Suppose the contact list consists of (for example) musicians and
teachers. For every person in the list, we keep name, address, etc.
For musicians, we keep track of what instruments they play, and
whether they sing or not. For teachers, we keep track of what subject
they teach, and what grade they teach ... but we also keep track of
the school's name and address (because we might meet more than one
teacher at each school, and we don't want to input the school
information more than once.)

My two questions:

1. When entering a new contact on a form, is it possible to select
"musician or teacher," and whichever one you pick, you get the
specific questions related to each? (In other words, if you pick
musician, you're asked about instruments, band name, etc, and if you
pick teacher, you're asked about school name, grade, etc.)??

2. I understand that there will be a separate table for teachers,
which would include (for example) school name and address, but would
there also be a separate table for musicians? What would it include?

The ultimate goal is to be able to (for example) find what was the
name of that saxophone player from Ohio, or who do we know who teaches
7th-grade math. At at the same time, we might need to find anyone's
e-mail address, or do a mail-merge to everyone. I think that I can
figure out the report part, but inputting the data in a convenient way
(as described above) is the part I don't get.

Again, I know this is real basic stuff. Thanks in advance for any
tips!