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 for the person and the dog's current status.
i would like to keep a record of any changes to a dog's status, i.e. when they're fostered (and by whom) and then when they're adopted (and by whom). i created another table (statusUpdate) that has FKs for the dog, the person and the status and i also have a date field.
my issue is that i don't know how to update both the statusUpdate table as well as the dog table to reflect what the current status is and who the dog is with.
i'm pretty sure it's a case of me not grasping a pretty simple concept. any assistance would be greatly appreciated.
regards,
g
- If you have a status update table, you no longer need the current status and current person fields in the dog table. If you want to know them, you can look them up in the status record of the dog in question with the highest date.
thanks. so i currently use a form to add people and another to add dogs. i take it that i would then need another entry form to add the link between the person and the dog and the dog's status?
i'm trying to make data entry as simple as possible as the people likely to use the DB are not the most computer literate. 🙂
thanks again,
g
- It is usual to use a subform in such a case. You can choose to create a status subform for the dogs form. Fill in the status and the person on the subform. It is also possible to create a subform for person. The dog approach seems the most logical to me.
- 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.
