|
| 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 |
|
#1
February 8th, 2009, 06:35 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Design help for community group database
My previous posts on this topic appear to have been deleted so I can't check
back on previous responses so I apologise for any repetition. I am trying to create a database that allows me to track the training, awards, roles, length of service etc of Girl Guide Leaders. Requirements 1)Each leader requires a Guiding Partner (mentor) for both initial qualification and further development. At each stage this may be a different person. 2)Each leader may be a Guiding Partner, but is not necessarily so. 3)A leader may be a GP for more than one leader, and each leader may have more than one GP. 4)Each leader is required to undertake qualification training and have that “appraised” every 3 years by completing further training courses and completing modules. Each module requires a GP 5)Long Service and Good Service Awards are presented and need to be tracked. Following advice from Allen Browne (which I hope I have understood) I have come up with the following tables, but I am still having trouble understanding how to set the many-many relationships...do I have even have the correct fields and correct primary keys I wonder? I understand setting the relationships when there are only 2 or 3 tables, but with the 15 I have I am still very confused. Perhaps I have misunderstood what to do with junction tables. tblLeaders LeaderID (primary key) Membership No FirstName LastName District Division Region HomePhone WorkPhone Mobile Address City State Postcode HusbandPartnerName tblRoles RoleID (primary key) Role Name tblRoleDetails (junction table) RoleID LeaderID tblModules ModuleID (primary key) ModuleName Freq PeriodID tblModulesCompleted QualID (primary key) LeaderID ModuleID StartDate Endate tblModuleDetails (junction table) ModuleID QualID tblTrainingCourses CourseID CourseName Freq PeriodID tblTrainingsAttended TrainingID LeaderID CourseID tblTrainingDetails (junction table) CourseID TrainingID tblGuidingPartners GPID LeaderID tblGP Allocations GPallocateID LeaderID ModuleID StartDate EndDate tblAwards AwardID AwardName tblAwardsReceived AwardsReceivedID LeaderID DateReceived tblAward Details (junction table) AwardID AwardsReceivedID *** Do I have too many junction tables? Is it possible to have one junction table to relate all the tables and if so what would its fields need to be? Thank for any help. I'm sorry to be so dense. 
|
|
#2
February 8th, 2009, 01:38 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Design help for community group database
Hi Anne
That's probably too big a question to answer in the groups here. We can't go through tens of tables advising on the relationships. I have mocked up a really basic example to illustrate just the tables for the mentoring part: http://allenbrowne.com/temp/GirlGuides.zip It's a 46KB (zipped) MDB for Access 2000 or later. In this example, tblClient holds everyone (guides, leaders, guiding partners, etc.) tblRole lists the possible roles people may have. tblClientRole lists the actual roles, so if client number 5 is a guiding partner, they this table defines it. You can then use a query (like qryGuide) to give you a listing of those who are guides, and a similar query to give you those who are leaders, and so on. tblMentor holds the information on who is mentoring whom. So, if client 1 (a guide) is being mentored by client 3 (a leader), you have a record that shows this relationship started on a particular date. Later, if client 1 will be mentored by client 2, you have another row that shows the date when that started. You can then construct queries to show who is currently mentoring whom. I didn't create any forms, but hopefully it's enough to show how the tables fit together. The Relationships widow gives the overview. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "AnneS" wrote in message ... My previous posts on this topic appear to have been deleted so I can't check back on previous responses so I apologise for any repetition. I am trying to create a database that allows me to track the training, awards, roles, length of service etc of Girl Guide Leaders. Requirements 1)Each leader requires a Guiding Partner (mentor) for both initial qualification and further development. At each stage this may be a different person. 2)Each leader may be a Guiding Partner, but is not necessarily so. 3)A leader may be a GP for more than one leader, and each leader may have more than one GP. 4)Each leader is required to undertake qualification training and have that “appraised” every 3 years by completing further training courses and completing modules. Each module requires a GP 5)Long Service and Good Service Awards are presented and need to be tracked. Following advice from Allen Browne (which I hope I have understood) I have come up with the following tables, but I am still having trouble understanding how to set the many-many relationships...do I have even have the correct fields and correct primary keys I wonder? I understand setting the relationships when there are only 2 or 3 tables, but with the 15 I have I am still very confused. Perhaps I have misunderstood what to do with junction tables. tblLeaders LeaderID (primary key) Membership No FirstName LastName District Division Region HomePhone WorkPhone Mobile Address City State Postcode HusbandPartnerName tblRoles RoleID (primary key) Role Name tblRoleDetails (junction table) RoleID LeaderID tblModules ModuleID (primary key) ModuleName Freq PeriodID tblModulesCompleted QualID (primary key) LeaderID ModuleID StartDate Endate tblModuleDetails (junction table) ModuleID QualID tblTrainingCourses CourseID CourseName Freq PeriodID tblTrainingsAttended TrainingID LeaderID CourseID tblTrainingDetails (junction table) CourseID TrainingID tblGuidingPartners GPID LeaderID tblGP Allocations GPallocateID LeaderID ModuleID StartDate EndDate tblAwards AwardID AwardName tblAwardsReceived AwardsReceivedID LeaderID DateReceived tblAward Details (junction table) AwardID AwardsReceivedID *** Do I have too many junction tables? Is it possible to have one junction table to relate all the tables and if so what would its fields need to be? Thank for any help. I'm sorry to be so dense.
|
|
#3
February 9th, 2009, 02:08 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Design help for community group database
I think that you are better off on your current track, but FYI there is
software something like this called TroopMaster.
|
|
#4
February 10th, 2009, 02:31 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Design help for community group database
Roger Carlson has a great zip file using scouts as an example in
normalization...it may help you learn about normalization and development of the design. http://www.rogersaccesslibrary.com/T.../GSADesign.zip "AnneS" wrote: My previous posts on this topic appear to have been deleted so I can't check back on previous responses so I apologise for any repetition. I am trying to create a database that allows me to track the training, awards, roles, length of service etc of Girl Guide Leaders. Requirements 1)Each leader requires a Guiding Partner (mentor) for both initial qualification and further development. At each stage this may be a different person. 2)Each leader may be a Guiding Partner, but is not necessarily so. 3)A leader may be a GP for more than one leader, and each leader may have more than one GP. 4)Each leader is required to undertake qualification training and have that “appraised” every 3 years by completing further training courses and completing modules. Each module requires a GP 5)Long Service and Good Service Awards are presented and need to be tracked. Following advice from Allen Browne (which I hope I have understood) I have come up with the following tables, but I am still having trouble understanding how to set the many-many relationships...do I have even have the correct fields and correct primary keys I wonder? I understand setting the relationships when there are only 2 or 3 tables, but with the 15 I have I am still very confused. Perhaps I have misunderstood what to do with junction tables. tblLeaders LeaderID (primary key) Membership No FirstName LastName District Division Region HomePhone WorkPhone Mobile Address City State Postcode HusbandPartnerName tblRoles RoleID (primary key) Role Name tblRoleDetails (junction table) RoleID LeaderID tblModules ModuleID (primary key) ModuleName Freq PeriodID tblModulesCompleted QualID (primary key) LeaderID ModuleID StartDate Endate tblModuleDetails (junction table) ModuleID QualID tblTrainingCourses CourseID CourseName Freq PeriodID tblTrainingsAttended TrainingID LeaderID CourseID tblTrainingDetails (junction table) CourseID TrainingID tblGuidingPartners GPID LeaderID tblGP Allocations GPallocateID LeaderID ModuleID StartDate EndDate tblAwards AwardID AwardName tblAwardsReceived AwardsReceivedID LeaderID DateReceived tblAward Details (junction table) AwardID AwardsReceivedID *** Do I have too many junction tables? Is it possible to have one junction table to relate all the tables and if so what would its fields need to be? Thank for any help. I'm sorry to be so dense.
|
|
#5
February 12th, 2009, 05:02 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Design help for community group database
Ah! Now I think I understand what you are on about. Being able to see your
working of my scenario in the relationships window, rather than just listing here makes it a bit clearer. Thanks so much for your time and patience with me. I'll carry on and hopefully will end up with a successful database! Anne "Allen Browne" wrote: Hi Anne That's probably too big a question to answer in the groups here. We can't go through tens of tables advising on the relationships. I have mocked up a really basic example to illustrate just the tables for the mentoring part: http://allenbrowne.com/temp/GirlGuides.zip It's a 46KB (zipped) MDB for Access 2000 or later. In this example, tblClient holds everyone (guides, leaders, guiding partners, etc.) tblRole lists the possible roles people may have. tblClientRole lists the actual roles, so if client number 5 is a guiding partner, they this table defines it. You can then use a query (like qryGuide) to give you a listing of those who are guides, and a similar query to give you those who are leaders, and so on. tblMentor holds the information on who is mentoring whom. So, if client 1 (a guide) is being mentored by client 3 (a leader), you have a record that shows this relationship started on a particular date. Later, if client 1 will be mentored by client 2, you have another row that shows the date when that started. You can then construct queries to show who is currently mentoring whom. I didn't create any forms, but hopefully it's enough to show how the tables fit together. The Relationships widow gives the overview. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org.
|
|
#6
February 12th, 2009, 05:03 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Design help for community group database
Thanks Fred, Yes I checked it out last time, but it really was too hard me to adapt. Thanks for the suggestion though. "Fred" wrote: I think that you are better off on your current track, but FYI there is software something like this called TroopMaster.
|
|
#7
February 12th, 2009, 05:04 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Design help for community group database
Thanks Maarkr. Will check it out. "Maarkr" wrote: Roger Carlson has a great zip file using scouts as an example in normalization...it may help you learn about normalization and development of the design. http://www.rogersaccesslibrary.com/T.../GSADesign.zip "AnneS" wrote: My previous posts on this topic appear to have been deleted so I can't check back on previous responses so I apologise for any repetition. I am trying to create a database that allows me to track the training, awards, roles, length of service etc of Girl Guide Leaders. Requirements 1)Each leader requires a Guiding Partner (mentor) for both initial qualification and further development. At each stage this may be a different person. 2)Each leader may be a Guiding Partner, but is not necessarily so. 3)A leader may be a GP for more than one leader, and each leader may have more than one GP. 4)Each leader is required to undertake qualification training and have that “appraised” every 3 years by completing further training courses and completing modules. Each module requires a GP 5)Long Service and Good Service Awards are presented and need to be tracked. Following advice from Allen Browne (which I hope I have understood) I have come up with the following tables, but I am still having trouble understanding how to set the many-many relationships...do I have even have the correct fields and correct primary keys I wonder? I understand setting the relationships when there are only 2 or 3 tables, but with the 15 I have I am still very confused. Perhaps I have misunderstood what to do with junction tables. tblLeaders LeaderID (primary key) Membership No FirstName LastName District Division Region HomePhone WorkPhone Mobile Address City State Postcode HusbandPartnerName tblRoles RoleID (primary key) Role Name tblRoleDetails (junction table) RoleID LeaderID tblModules ModuleID (primary key) ModuleName Freq PeriodID tblModulesCompleted QualID (primary key) LeaderID ModuleID StartDate Endate tblModuleDetails (junction table) ModuleID QualID tblTrainingCourses CourseID CourseName Freq PeriodID tblTrainingsAttended TrainingID LeaderID CourseID tblTrainingDetails (junction table) CourseID TrainingID tblGuidingPartners GPID LeaderID tblGP Allocations GPallocateID LeaderID ModuleID StartDate EndDate tblAwards AwardID AwardName tblAwardsReceived AwardsReceivedID LeaderID DateReceived tblAward Details (junction table) AwardID AwardsReceivedID *** Do I have too many junction tables? Is it possible to have one junction table to relate all the tables and if so what would its fields need to be? Thank for any help. I'm sorry to be so dense.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
