How to track appointment to multi boads.

[mledarney]

I am modifing a database that track appoinment to various boards. Each person may be appointed to more then one board. I have been asked to add the ability to on the existing forn add the ability to ender the name of each board a person is on, with the thought that the function to list all members to a board could also be added.

currently there is a single table and one query for a drop down field on the form.

Any help or suggestions welcome.

thanks.

 

[KenReay]

Hi

To begin

You need three tables

tblPerson
PersonId (PK)
..etc

tblBoard
BoardId (PK)
...etc

tblPersonOnBoard
PersonId )PK
BoardId )


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[willir]

Ken is bang on

You have a many-to-many relationship between people and boards.

A couple of tricks...

tblBoard
BoardId (Primary Key)
...
BoardSize
Quorum
...etc

...so you can see which boards have a need of new board members.


tblPersonOnBoard
PersonId )PK
BoardId )
Term ( 1st, 2nd..)
DateJoined
TermEnds - date field to track when their term ends.

You also probably want to track position - Chair, President, VP, etc. You can do this tblPersonOnBoard by adding position. You can accomplish the same thing by have fields on the table tblBoard ChairID points to PersonID on tblPerson

If you need to track history, who was President for 2003, then you need to add another table...

tblPosition
Term - For example, year
BoardID
PersonID
Position

One gotcha is if person resigns and is replaced.

Richard

 

[BSman]

Ken and Richard have the right ideas. One suggestion: Instead of Richard's table called tblPosition containing the personID, I'd suggest that it would have a PositionID PK column and the position title. If you want to be sure that only positions applicable to each board can be selected you would include the BoardID column, otherwise, the table would contain all position titles needed. If you include the BoardID you might also want to include term information for each position (but that could get to be too much detail to maintain easily) and other position/board specific data.

Then your tblPersonOnBoard would also include the PositionID to indicate the person's position on the board.

Whichever table setup you use you will be in a good position to get a variety of useful reports etc. from the data by using a variety of queries.

 

[willir]

Yes BSam

I understand about the PositionID primary key on the tblPosition table. I pondered this since I did not know if this was a requriement, and there were other ways to track this. I was also concerned about duplicate entries vs resignations.

The problem is we don't know how complicated mledarney needs are. With my experience, boards can have multiple formats - president, vp, tres., sec.; pres with no vp; chair...; joint chairs. Or there could be a very straight forward structure to the table design.

By-the-way, mledarney, you can use this same design to track more than board members - committees, task forces, etc.

Richard

 

[KenReay]

Hi

It looks as if we are talking to ourselves here, that is why I kept it simple in my original post

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK