Forms

[Jennie]

I am just new to Access. I have created a form with contacts, names,
address, telephone numbers etc for a company which has several Boards and
Committees. I would now like to add the various boards or committees they
belong to, i.e, the company name is The Brown Company, I have 100
shareholders in my contacts list. There are several boards, ie. the Brown
Investment Board, the Brown Real Estate Board, the Brown Legal Sevices Board
(in all there are about 40 different committees and boards). Within those
Boards I would like to identify which roles or positions the various
shareholders hold, i.e. Chairman, Secretary, Director. For instance, John
Brown is a member of several of the Boards, he is Chairman of some and
Company Secretary of another and just a Director of others. I don't think I
can use a combo box because they are not just a member of one board, but
several as I explained. Can someone explain the best way to do this, do I
create another two tables? If so how do I link them, do I add them to the
bottom of the original form, if so how? I would be very grateful to have a
simple explanation. Many thanks and kind regards.

 

[Jochen]

Jennie,

You have a many-to-many relationship between your contacts and boards.
Because one person can be a member of multiple boards. and one board can
hold multiple members.
Several solutions to your problem
1) you can accomodate multiple boards with multpile rows - one for each
contact. Problem is that all the data for each contact has to be repeated
which is an obvious case of redundancy
2) you can accomodate multiple boards with multiple columns in a single row.
Problem here is that you don't really know how many colums you will need.
This will create a lot of empty fields
3) you can include all boards in one column. Problem here is searching the
table in the future. And how to create an alphabetical list of boards?

The way I would do it, is to break up the many-to-many relationship into two
one-to-many relationships
that means creating a table (eg Contact/Boards) that holds the ID's of your
contacts and the ID's of the corresponding boards
eg:
If John Browns ID is 12
and he is member of board 45, 48 and 69 the table would look like

ContactID BoardID
12 45
12 48
12 69

Put this table between your other tables making the many-to-many
relationship into two one-to-many relationships

this way you avoid bloating your database. Make sure you use cascading
deletions and cascading updates, or you will wind up with dangling
references. this means when you delete a board or a contact, the reference
will still exist in this other table. By doubleclicking the relation you can
tell access to delete or update this new table when ever you make a change in
the other tables (Enforce referential integrity)


For more information i suggest googling or using microsoft help.

 

[Jochen]

Jennie,

I forgot the part of the positions of the contacts, just add a third column
to the table which holds the position (chairman, secretary, director), best
way is to also do this in a seperate table and use relationships

I know this may sound difficult to someone who is new to access, but there
is enough information out there on the world wide web to help you. Hey, even
I am an access newbie ;-)

 

[Jennie]

Jochen

Thank you so much for your help, I sort of had that in mind but couldn't
quite articulate it. I'll give it a try.

Have a lovely day and thanks again.

Jennie