If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Need help with Tables Design and Relationships
Yes, it is a valid process, but I would start much simpler first. Just query
a couple of tables first. Then, when you are sure it is giving you the right data, add another, etc. That will, hopefully, lead you to realize where the connections are incorrect. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... I have been doing this for testing purposes. I was under the impression that running such "testing query" would determine whether or the relationship between all tables and fields are joined properly. The way you sound, this is not a valid process for checking the relationships, right? -- Thanks, Tom "Lynn Trapp" wrote in message ... Tom, Why are you trying to select EVERY field in ALL 6 tables???? -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... Lynn: Again, thanks for the feedback. I made the changes accordingly. All relationships are joined properly (I believe) in the relationship view. In query view, however, when I select every field of all 6 tables and then execute the query, "nothing" shows up. What I mean by "nothing" is that I don't even see a blank record. I simply see the gray field labels. In the past, this normally was an indication for me that something was not properly joined. Assuming that I have created the relationships as you suggested, should I expect to see "nothing" in the query that pulls all fields from all tables. I hope my terminology makes sense? -- Thanks, Tom "Lynn Trapp" wrote in message ... Tom, I think I may have led you astray with my terminology. You dont' want the AssigneeID in Both of those tables. Rather, you need to lookup the MemberID from the Employees table and store it in the AssigneeID of the BoardTaskAssignments table. Look at the modified layout below BoardTaskAssignmentID PK BoardTaskID FK (to tblBoardTasks) AssigneeID (do a lookup of EmployeeID from tblEmployees) ..other BoardTaskAssignment related fields tblEmployees EmployeeID PK ....Other Employee related fields -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... Lynn: Again, thanks for the feedback. Please bear with me on this. Okay, I made some small progress but I'm still uncertain about the relationship between tblEmployees and tblBoardTaskAssignments. tblEmployees: Pk: MemberID (Text data type); 1:M to MemberIDfk of tblBoardMembers (that's okay right now) AssigneeID (Text data type) tblBoardTaskAssignments: Pk: BoardTaskAssignmentsID AssigneeID (Text data type) My Questions: 1. Right now, when creating the join between tblEmployees.AssigneeID & tblBoardTaskAssignments.AssigneeID, I get an "Indeterminate Relationship" error? What am I doing wrong? Does either of the AssigneeID needs to become a composite PK? 2. When you mentioned that Skills set should be a "subset" of the Employee records, did you mean that "Skill Set" will be a field of the tblEmployees or did you mean that tblSkillSet is a child table of tblEmployees? 3. Just in Q2, will priorities & budget categories become fields of tblProjects or will they become tables themselves? BTW, I appreciate your mentioning to provide me an ER diagram? I believe that really would help me better understand your design. Again, I truly appreciate your help in this. Thanks in advance, Tom "Lynn Trapp" wrote in message ... Answers inline below -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... Lynn: I had an intial look at this... I followed your recommendations and developed the tables as suggested. Here are now some additional questions: tblBoards: 1. Is tblBoards a "junction table" for tblBoardMembers & tblBoardTasks No, tblBoardMembers is a "junction table" between tblBoards and tblEmployees 2. If yes, should BoardIDPK become an "Autonumber" It can be or you can use some natural key instead 3. If yes, do you then recommend to add 2 additional Number fields (Long Integers) and make all 3 of them composite PKs? Otherwise, can I link two foreign keys (BoardMemberID and BoardTaskID) to one and the same Autonumber (BoardIDPK)? I don't think you have quite understood what I'm after here. I will try to take the time to draw out a better ER diagram and get it to you next week. tblEmployees: 1. Is this a "stand-alone" table that is used as a source for MemberIDfk and AssigneeIDfk (both of the FKs are then Text fields, right?) Yes Other tables: 1. How do I integrate a) tblProjects, b) tblSkillSets, c) tblPriorities, and d) tblBudgetCategories into the existing schema? I would see Skill sets as a subset of the Employee records and Priorities and BudgetCategories as subsets of Projects 2. I believe between all of these, there could be a M:M relationship? I appreciate any additional help on this. And (please) keep in mind that I'm just learning more about the relational design, so I may ask some beginner level questions. Thanks so much in advance, Lynn. Tom "Lynn Trapp" wrote in message ... You're quite welcome, Tom. I hope it helps out. I will be sure to monitor this thread. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... Lynn: Thanks for the prompt reply and help in this matter. I will attempt to design the architecture over the course of the weekend. If I get stuck on something, I will post another message in this thread... in hope that you might check it again. Thanks so much!!! I truly appreciate your feedback. Tom "Lynn Trapp" wrote in message ... Here is ONE way to design this. I have only listed the tables necessary for the Boards. You would need to extend the same thing for Projects. Keep in mind that this approach assumes that Boards and Projects are totally separate. If Boards can have projects then you would have to go a different route. Also, while you way there is only one organization, this design allows for multiples, if the need ever arises. Please feel free to post back with further questions. tblOrganization Organization_id PK ..Other organization fields tblBoard BoardID PK OrganizationID FK (to tblOrganization) .Other Board Fields tblBoardMembers BoardMemberID PK BoardID FK (to tblBoard) MemberID (do a lookup from tblEmployees) .Other BoardMember related fields tblBoardTasks BoardTaskID PK BoardID FK (to tblBoard) .other BoardTask related fields tblBoardTaskAssignments BoardTaskAssignmentID PK BoardTaskID FK (to tblBoardTasks) AssigneeID (do a lookup from tblEmployees) ..other BoardTaskAssignment related fields tblEmployees EmployeeID PK ....Other Employee related fields -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... I need to come up with a design for the tables listed below. Although I provided some general information about the relationships as well, I am not too sure as to how the actual table joins should be designed. I truly reply on someone's expertise here (reading between the lines) and hope to get some ideas how the table design might work. Here are the tables: - Organization - Boards - Employees - Tasks - Priorities - Projects - Budget Categories - Skill Sets Again, I don't have all the exact information myself as to how the tables should be joined. Maybe, there's even a chance that I don't need all the tables listed above. But, let me try to provide some more information: Organization: 1. There is 1 organization 2. The organization has multiple boards 3. The organization has multiple projects 4. There are multiple tasks that either result from the boards or the projects 5. Naturally, there are multiple members in the organization that can be assigned to a) boards, b) projects ) boards and projects Boards: 1. Multiple boards will have multiple members 2. As a result of board meetings, there will be multiple tasks 3. These tasks will have different priorities 4. The tasks will be subsidized via different funds (budget categories) Employees: 1. Multiple employees can be assigned to multiple boards 2. Multiple employees can be assigned to multiple projects 3. All employees have different skills sets (multiple skills sets) 4. The employees will be assigned to different tasks Budget Categories, Skill Sets have been listed in the description above. Not sure how else they could be further described here. Again, if I could get some ideas as to how best construct this, I would really appreciate it!!! Any ideas would really help me in my job. THANKS! Please don't hesitate to post additional questions if I didn't provide sufficient information. Thanks so much, Tom |
#22
|
|||
|
|||
Need help with Tables Design and Relationships
Thanks, I'll proceed with step-by-step validation.
-- Thanks, Tom "Lynn Trapp" wrote in message ... Yes, it is a valid process, but I would start much simpler first. Just query a couple of tables first. Then, when you are sure it is giving you the right data, add another, etc. That will, hopefully, lead you to realize where the connections are incorrect. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... I have been doing this for testing purposes. I was under the impression that running such "testing query" would determine whether or the relationship between all tables and fields are joined properly. The way you sound, this is not a valid process for checking the relationships, right? -- Thanks, Tom "Lynn Trapp" wrote in message ... Tom, Why are you trying to select EVERY field in ALL 6 tables???? -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... Lynn: Again, thanks for the feedback. I made the changes accordingly. All relationships are joined properly (I believe) in the relationship view. In query view, however, when I select every field of all 6 tables and then execute the query, "nothing" shows up. What I mean by "nothing" is that I don't even see a blank record. I simply see the gray field labels. In the past, this normally was an indication for me that something was not properly joined. Assuming that I have created the relationships as you suggested, should I expect to see "nothing" in the query that pulls all fields from all tables. I hope my terminology makes sense? -- Thanks, Tom "Lynn Trapp" wrote in message ... Tom, I think I may have led you astray with my terminology. You dont' want the AssigneeID in Both of those tables. Rather, you need to lookup the MemberID from the Employees table and store it in the AssigneeID of the BoardTaskAssignments table. Look at the modified layout below BoardTaskAssignmentID PK BoardTaskID FK (to tblBoardTasks) AssigneeID (do a lookup of EmployeeID from tblEmployees) ..other BoardTaskAssignment related fields tblEmployees EmployeeID PK ....Other Employee related fields -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... Lynn: Again, thanks for the feedback. Please bear with me on this. Okay, I made some small progress but I'm still uncertain about the relationship between tblEmployees and tblBoardTaskAssignments. tblEmployees: Pk: MemberID (Text data type); 1:M to MemberIDfk of tblBoardMembers (that's okay right now) AssigneeID (Text data type) tblBoardTaskAssignments: Pk: BoardTaskAssignmentsID AssigneeID (Text data type) My Questions: 1. Right now, when creating the join between tblEmployees.AssigneeID & tblBoardTaskAssignments.AssigneeID, I get an "Indeterminate Relationship" error? What am I doing wrong? Does either of the AssigneeID needs to become a composite PK? 2. When you mentioned that Skills set should be a "subset" of the Employee records, did you mean that "Skill Set" will be a field of the tblEmployees or did you mean that tblSkillSet is a child table of tblEmployees? 3. Just in Q2, will priorities & budget categories become fields of tblProjects or will they become tables themselves? BTW, I appreciate your mentioning to provide me an ER diagram? I believe that really would help me better understand your design. Again, I truly appreciate your help in this. Thanks in advance, Tom "Lynn Trapp" wrote in message ... Answers inline below -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... Lynn: I had an intial look at this... I followed your recommendations and developed the tables as suggested. Here are now some additional questions: tblBoards: 1. Is tblBoards a "junction table" for tblBoardMembers & tblBoardTasks No, tblBoardMembers is a "junction table" between tblBoards and tblEmployees 2. If yes, should BoardIDPK become an "Autonumber" It can be or you can use some natural key instead 3. If yes, do you then recommend to add 2 additional Number fields (Long Integers) and make all 3 of them composite PKs? Otherwise, can I link two foreign keys (BoardMemberID and BoardTaskID) to one and the same Autonumber (BoardIDPK)? I don't think you have quite understood what I'm after here. I will try to take the time to draw out a better ER diagram and get it to you next week. tblEmployees: 1. Is this a "stand-alone" table that is used as a source for MemberIDfk and AssigneeIDfk (both of the FKs are then Text fields, right?) Yes Other tables: 1. How do I integrate a) tblProjects, b) tblSkillSets, c) tblPriorities, and d) tblBudgetCategories into the existing schema? I would see Skill sets as a subset of the Employee records and Priorities and BudgetCategories as subsets of Projects 2. I believe between all of these, there could be a M:M relationship? I appreciate any additional help on this. And (please) keep in mind that I'm just learning more about the relational design, so I may ask some beginner level questions. Thanks so much in advance, Lynn. Tom "Lynn Trapp" wrote in message ... You're quite welcome, Tom. I hope it helps out. I will be sure to monitor this thread. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... Lynn: Thanks for the prompt reply and help in this matter. I will attempt to design the architecture over the course of the weekend. If I get stuck on something, I will post another message in this thread... in hope that you might check it again. Thanks so much!!! I truly appreciate your feedback. Tom "Lynn Trapp" wrote in message ... Here is ONE way to design this. I have only listed the tables necessary for the Boards. You would need to extend the same thing for Projects. Keep in mind that this approach assumes that Boards and Projects are totally separate. If Boards can have projects then you would have to go a different route. Also, while you way there is only one organization, this design allows for multiples, if the need ever arises. Please feel free to post back with further questions. tblOrganization Organization_id PK ..Other organization fields tblBoard BoardID PK OrganizationID FK (to tblOrganization) .Other Board Fields tblBoardMembers BoardMemberID PK BoardID FK (to tblBoard) MemberID (do a lookup from tblEmployees) .Other BoardMember related fields tblBoardTasks BoardTaskID PK BoardID FK (to tblBoard) .other BoardTask related fields tblBoardTaskAssignments BoardTaskAssignmentID PK BoardTaskID FK (to tblBoardTasks) AssigneeID (do a lookup from tblEmployees) ..other BoardTaskAssignment related fields tblEmployees EmployeeID PK ....Other Employee related fields -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... I need to come up with a design for the tables listed below. Although I provided some general information about the relationships as well, I am not too sure as to how the actual table joins should be designed. I truly reply on someone's expertise here (reading between the lines) and hope to get some ideas how the table design might work. Here are the tables: - Organization - Boards - Employees - Tasks - Priorities - Projects - Budget Categories - Skill Sets Again, I don't have all the exact information myself as to how the tables should be joined. Maybe, there's even a chance that I don't need all the tables listed above. But, let me try to provide some more information: Organization: 1. There is 1 organization 2. The organization has multiple boards 3. The organization has multiple projects 4. There are multiple tasks that either result from the boards or the projects 5. Naturally, there are multiple members in the organization that can be assigned to a) boards, b) projects ) boards and projects Boards: 1. Multiple boards will have multiple members 2. As a result of board meetings, there will be multiple tasks 3. These tasks will have different priorities 4. The tasks will be subsidized via different funds (budget categories) Employees: 1. Multiple employees can be assigned to multiple boards 2. Multiple employees can be assigned to multiple projects 3. All employees have different skills sets (multiple skills sets) 4. The employees will be assigned to different tasks Budget Categories, Skill Sets have been listed in the description above. Not sure how else they could be further described here. Again, if I could get some ideas as to how best construct this, I would really appreciate it!!! Any ideas would really help me in my job. THANKS! Please don't hesitate to post additional questions if I didn't provide sufficient information. Thanks so much, Tom |
#23
|
|||
|
|||
Need help with Tables Design and Relationships
----- Lynn Trapp wrote: -----
snip BoardTaskAssignmentID PK BoardTaskID FK (to tblBoardTasks) AssigneeID (do a lookup of EmployeeID from tblEmployees) ...other BoardTaskAssignment related fields Hi Lynn, Just for clarification - In the above table description did you mean to use a combo box on a form with a recordsource of the employee table, or make the AssigneeID field a lookup field in the BoardTaskAssignment table? thanks |
#24
|
|||
|
|||
Need help with Tables Design and Relationships
rpw,
I ALWAYS do lookups from a combobox on a form. I NEVER, NEVER, NEVEr use a lookup field at the table level. Some say there are some situations where it might be good -- like initial development -- but I don't agree and don't do it. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... ----- Lynn Trapp wrote: ----- snip BoardTaskAssignmentID PK BoardTaskID FK (to tblBoardTasks) AssigneeID (do a lookup of EmployeeID from tblEmployees) ...other BoardTaskAssignment related fields Hi Lynn, Just for clarification - In the above table description did you mean to use a combo box on a form with a recordsource of the employee table, or make the AssigneeID field a lookup field in the BoardTaskAssignment table? thanks |
#25
|
|||
|
|||
Need help with Tables Design and Relationships
Hi Lynn,
I sorta figured that, but by mentioning "lookup" in the table description I wasn't certain and I figured that other newbies that might be following this thread might not understand your true meaning. thanks again, rpw ----- Lynn Trapp wrote: ----- rpw, I ALWAYS do lookups from a combobox on a form. I NEVER, NEVER, NEVEr use a lookup field at the table level. Some say there are some situations where it might be good -- like initial development -- but I don't agree and don't do it. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... ----- Lynn Trapp wrote: ----- snip BoardTaskAssignmentID PK BoardTaskID FK (to tblBoardTasks) AssigneeID (do a lookup of EmployeeID from tblEmployees) ...other BoardTaskAssignment related fields Hi Lynn, Just for clarification - In the above table description did you mean to use a combo box on a form with a recordsource of the employee table, or make the AssigneeID field a lookup field in the BoardTaskAssignment table? thanks |
Thread Tools | |
Display Modes | |
|
|