Forum Discussion
struggling to grasp the concept...
Hi, i am attempting an access DB for a dog rescue. i have a table for people, one for dogs and one for status' (i.e. been surrendered, been fostered, been adopted). in my dog table i have FK fo...
hi again, the struggles are real! i am now attempting to record (using a form) a list of committee members. i have 3 tables 1 - people 2 - committee roles 3 - committee the committee table has foreign keys for people and roles and also records a commencement date and a ceased date. ideally i would like a combo box that has the roles, displays who is currently in that role (when they commenced) and allows the ability to assign a ceased date when they leave the role. a person would be able to hold more than one role. with my current attempts the person doesn't change when the position is selected in the combo. i've also had problems adding roles to a person who has an existing role, the table just updates their role rather than adding another record. FML... any tips would be greatly received. regards, g
You need at least one more table. A committee has multiple people and a person can work in more than one committee. So you need a junction table (people-committee) with (so far) two foreign keys (no foreign key to people in the committee table).
A person has a role in a committee. Someone else can have the same role in an other committee. A person can have (different) roles in the committees he is in. Bottom line is: role is an attribute (fk) in people-committee (no fk to role in committee). I assume a person has only ONE role in a committees.
- thanks. there is only one committee, there is the possibility that a person may hold more than one role in the committee. it's only a small non-profit organisation.
- also, is there a way that i can have the 'isActive' field changed from yes to no when a ceaseDate is added to the persons role. e.g. when a committee member resigns i would enter the date of that as their ceaseDate which would mean that their position was now available for someone else to perform. i was trying to use the isActive field to show only those roles that no one is currently performing on the form.
- Why do you need a committee table when there is only one committee?
In addition to the tables for people and roles, you need a table to link them. So there are two foreign keys in it.
An isActive field is not necessary. If the ceaseDate is filled in, then the person is not active, otherwise he is. So that is deducible and you don't store it.