HadererDirndl
Registered User.
- Local time
- Today, 09:19
- Joined
- Jan 25, 2016
- Messages
- 19
Hello,
I am designing a database to manage workshops with MSAccess 2010, and it is not behaving as I expected. I am sensing I have a problem with relationships.
The purpose of the database is to manage workshops. Each workshop is held at a specific facility, and we have workshops at the same facility multiple times a year. The facilities can have different rooms we can use, and each facility also can have a number of caretakers who work with us. To reflect this, I have four tables: tblWorkshops, tblFacilities, tblCaretakers, and tblFacilityRooms. The PK of tblFacilities, “FacilityID”, is used as a foreign key in the other three tables, with a relationship of 1:n (every facility can have multiple workshops, multiple rooms, and multiple caretakers). The PKs of tblCaretakers and tblFacilityRooms (“CaretakerID” and “FacilityRoomID”) are also foreign keys in tblWorkshops, with a 1:n relationship (every caretaker and room can have multiple workshops, every workshop only has one room and caretaker).
What I now want to do is to create a form for Workshop Setup to enter data into tblWorkshops. I was expecting that I select FacilityID from the foreign key lookup I created, and that this would then enable me to constrain my choices in the lookups for “CaretakerID” and “FacilityRoomID”. But it doesn’t. I am able to select ANY room and ANY caretaker, regardless of what facility they are mapped to.
My question is: Are the relationships I created even acceptable? There is a bit of a love triangle going on, because tblWorkshops, tblFacilities, and tblCaretakers (or tblFacilityRooms) all have a relationship with the two other ones. I also realize that it is a bit redundant: When I know which room or caretaker will be assigned to a class, I know automatically what the facility must be. However, from a user perspective, we typically know the facility upfront when creating a workshop, and assign room and caretaker long after the workshop was created in the database. Also, “CaretakerID” and “FacilityRoomID may not ever be assigned to a workshop at all (the fields are not “required” to be filled in tblWorkshops).
So I definitely need FacilityID in my tblWorkshops somehow, and cannot rely on CaretakerID and FacilityRoomID to be present.
If the relationships are valid – how do I get Access to constrain my choices on the form, based on the selection of FacilityID? Do I have to set up a special query to handle this? Do I need some other kind of mapping table?
I hope my description makes sense, and that some here finds this interesting enough to help out. I would be very grateful.
I am designing a database to manage workshops with MSAccess 2010, and it is not behaving as I expected. I am sensing I have a problem with relationships.
The purpose of the database is to manage workshops. Each workshop is held at a specific facility, and we have workshops at the same facility multiple times a year. The facilities can have different rooms we can use, and each facility also can have a number of caretakers who work with us. To reflect this, I have four tables: tblWorkshops, tblFacilities, tblCaretakers, and tblFacilityRooms. The PK of tblFacilities, “FacilityID”, is used as a foreign key in the other three tables, with a relationship of 1:n (every facility can have multiple workshops, multiple rooms, and multiple caretakers). The PKs of tblCaretakers and tblFacilityRooms (“CaretakerID” and “FacilityRoomID”) are also foreign keys in tblWorkshops, with a 1:n relationship (every caretaker and room can have multiple workshops, every workshop only has one room and caretaker).
What I now want to do is to create a form for Workshop Setup to enter data into tblWorkshops. I was expecting that I select FacilityID from the foreign key lookup I created, and that this would then enable me to constrain my choices in the lookups for “CaretakerID” and “FacilityRoomID”. But it doesn’t. I am able to select ANY room and ANY caretaker, regardless of what facility they are mapped to.
My question is: Are the relationships I created even acceptable? There is a bit of a love triangle going on, because tblWorkshops, tblFacilities, and tblCaretakers (or tblFacilityRooms) all have a relationship with the two other ones. I also realize that it is a bit redundant: When I know which room or caretaker will be assigned to a class, I know automatically what the facility must be. However, from a user perspective, we typically know the facility upfront when creating a workshop, and assign room and caretaker long after the workshop was created in the database. Also, “CaretakerID” and “FacilityRoomID may not ever be assigned to a workshop at all (the fields are not “required” to be filled in tblWorkshops).
So I definitely need FacilityID in my tblWorkshops somehow, and cannot rely on CaretakerID and FacilityRoomID to be present.
If the relationships are valid – how do I get Access to constrain my choices on the form, based on the selection of FacilityID? Do I have to set up a special query to handle this? Do I need some other kind of mapping table?
I hope my description makes sense, and that some here finds this interesting enough to help out. I would be very grateful.
