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
|
|||
|
|||
Design help for community group database
I am trying to set up a database to track Girl Guide leaders in my Region
along with their training and awards history. Each leader has another leader as a Guiding Partner (or mentor) and this has to be reflected in their training history. Each leader's qualifications need to be "appraised" every 3 years and I need to be able to track when that is due. I also need to be able to get reports such as which leaders have certains training qualifications (modules) etc. Would appreciate any help. So far I have set up the following tables: LEADERS TABLE: MemberID (using Girl Guides membership number, not autonumber) primary key First Name Last Name District Division Region Position Home Phone Work Phone Mobile Address City State Postcode QUALIFICATION TABLE: QualID (autonumber) primary key Module Type Start Date Completion Date Expiry Date TRAINING COURSES TABLE TrainingID (autonumber) primary key Course Name Date attended AWARDS TABLE: AwardsID (autonumber) Primary key Award Type Date Received GUIDING PARTNERS TABLE GuidingPartnerID (autonumber) Primary key This is as far as I have gone as I haven't yet got my head around how to do the relationships, particularly in regards to foreign keys and object dependancies. I understand the concept of foreign keys, but not sure how to apply them with my scenario. The examples I have seen only have 3 tables so it is fairly simple to apply them. I have only a very basic knowledge of databases and I am getting confused by all the "techy" talk in the various online tutorials I have read, so I need advice is fairly simple language, sorry. 1)Am I on the right track? (ie have I designed the tables the best way) 2)Do I need any more tables for the relationships? Thanks for any help Anne |
#2
|
|||
|
|||
Design help for community group database
Anne, there's probably a fair bit more to it than the tables you posted. You
might like to ask around the other guide troops and see if there is something else in place that they use, to save re-inventing the wheel. If you do want to build something from scratch, I would suggest using one table for all people (leaders and members.) This will make foreign keys much easier (e.g. where you need to book leaders and members into an event/camp, or where you need to accept donations/payments from people.) If you want to retain a person's training history (who mentored whom when), the table would then have fields like this: ID AutoNumber primary key MentorID Number relates to Person.PersonID. Who does mentoring MenteeID Numbewr also relates to ". Who is mentored. StartDate Date/Time when the mentor started with this mentee. EndDate Date/Time when this ceased. (Blank if current.) Comment Text or Memo Regarding the training courses, one training course is offered multiple times over the years, and people enrol in one instance of the training course (e.g. the one starting in Nov 2008), not in the course itself. Depending on what you need, you may need to have a table of the actual dates that make up the instance of the training course, attendance records for students for each date, and so on. -- 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 ... I am trying to set up a database to track Girl Guide leaders in my Region along with their training and awards history. Each leader has another leader as a Guiding Partner (or mentor) and this has to be reflected in their training history. Each leader's qualifications need to be "appraised" every 3 years and I need to be able to track when that is due. I also need to be able to get reports such as which leaders have certains training qualifications (modules) etc. Would appreciate any help. So far I have set up the following tables: LEADERS TABLE: MemberID (using Girl Guides membership number, not autonumber) primary key First Name Last Name District Division Region Position Home Phone Work Phone Mobile Address City State Postcode QUALIFICATION TABLE: QualID (autonumber) primary key Module Type Start Date Completion Date Expiry Date TRAINING COURSES TABLE TrainingID (autonumber) primary key Course Name Date attended AWARDS TABLE: AwardsID (autonumber) Primary key Award Type Date Received GUIDING PARTNERS TABLE GuidingPartnerID (autonumber) Primary key This is as far as I have gone as I haven't yet got my head around how to do the relationships, particularly in regards to foreign keys and object dependancies. I understand the concept of foreign keys, but not sure how to apply them with my scenario. The examples I have seen only have 3 tables so it is fairly simple to apply them. I have only a very basic knowledge of databases and I am getting confused by all the "techy" talk in the various online tutorials I have read, so I need advice is fairly simple language, sorry. 1)Am I on the right track? (ie have I designed the tables the best way) 2)Do I need any more tables for the relationships? Thanks for any help Anne |
#3
|
|||
|
|||
Design help for community group database
"Allen Browne" wrote:
Anne, there's probably a fair bit more to it than the tables you posted. You might like to ask around the other guide troops and see if there is something else in place that they use, to save re-inventing the wheel. There's a software package call Troopmaster (http://www.troopmaster.com/) that does this for Boy Scouts at the Unit level. Not sure whether or not that would be helpful. Fred |
#4
|
|||
|
|||
Design help for community group database
" There's a software package call Troopmaster (http://www.troopmaster.com/)
that does this for Boy Scouts at the Unit level. Not sure whether or not that would be helpful. Fred" Thanks for the suggestion Fred. I have downloaded a demo version and as I suspected it is designed very much for American Scouting, and from my initial perusal, I don't know how easy it would be to adapt to Australian requirements, but more to the point we are not in the financial position to purchase such software, so a database that I can create with exisiting software would be better for me. Thanks for your help though Anne "Fred" wrote: "Allen Browne" wrote: Anne, there's probably a fair bit more to it than the tables you posted. You might like to ask around the other guide troops and see if there is something else in place that they use, to save re-inventing the wheel. There's a software package call Troopmaster (http://www.troopmaster.com/) that does this for Boy Scouts at the Unit level. Not sure whether or not that would be helpful. Fred |
#5
|
|||
|
|||
Design help for community group database
Thanks Allen for your suggestions. I have checked with others but most of
them seem to be using a variety of spreadsheets which aren't quite in a format that I would like. I also thought that a database would be a more efficient way of keeping the records. Our new state website (NSW) has a database and prior to its launch earlier this year, we thought that we would be able to do all our Region record-keeping using it, online. Unfortunately this is not possible at this time and in the interim we are required to keep local records, but I don't wish to use the inherited paper-based system. I only have to keep records for the leaders and I won't be needing to enter camping/event details (other than training courses) and donations/payments. I don't quite understand your suggestion for the Training Table, especially "MentorID Number relates to Person.PersonID. Who does mentoring MenteeID Numbewr also relates to ". Who is mentored." and how to implement it (sorry to be so obtuse, but I really am only a novice), but I will play around with it to see if I can work it out. Do I assume that I don't need the Guiding Partner table and that the fields mentioned above go into the training table? Thanks again for your help Anne "Allen Browne" wrote: Anne, there's probably a fair bit more to it than the tables you posted. You might like to ask around the other guide troops and see if there is something else in place that they use, to save re-inventing the wheel. If you do want to build something from scratch, I would suggest using one table for all people (leaders and members.) This will make foreign keys much easier (e.g. where you need to book leaders and members into an event/camp, or where you need to accept donations/payments from people.) If you want to retain a person's training history (who mentored whom when), the table would then have fields like this: ID AutoNumber primary key MentorID Number relates to Person.PersonID. Who does mentoring MenteeID Numbewr also relates to ". Who is mentored. StartDate Date/Time when the mentor started with this mentee. EndDate Date/Time when this ceased. (Blank if current.) Comment Text or Memo Regarding the training courses, one training course is offered multiple times over the years, and people enrol in one instance of the training course (e.g. the one starting in Nov 2008), not in the course itself. Depending on what you need, you may need to have a table of the actual dates that make up the instance of the training course, attendance records for students for each date, and so on. -- 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 ... I am trying to set up a database to track Girl Guide leaders in my Region along with their training and awards history. Each leader has another leader as a Guiding Partner (or mentor) and this has to be reflected in their training history. Each leader's qualifications need to be "appraised" every 3 years and I need to be able to track when that is due. I also need to be able to get reports such as which leaders have certains training qualifications (modules) etc. Would appreciate any help. So far I have set up the following tables: LEADERS TABLE: MemberID (using Girl Guides membership number, not autonumber) primary key First Name Last Name District Division Region Position Home Phone Work Phone Mobile Address City State Postcode QUALIFICATION TABLE: QualID (autonumber) primary key Module Type Start Date Completion Date Expiry Date TRAINING COURSES TABLE TrainingID (autonumber) primary key Course Name Date attended AWARDS TABLE: AwardsID (autonumber) Primary key Award Type Date Received GUIDING PARTNERS TABLE GuidingPartnerID (autonumber) Primary key This is as far as I have gone as I haven't yet got my head around how to do the relationships, particularly in regards to foreign keys and object dependancies. I understand the concept of foreign keys, but not sure how to apply them with my scenario. The examples I have seen only have 3 tables so it is fairly simple to apply them. I have only a very basic knowledge of databases and I am getting confused by all the "techy" talk in the various online tutorials I have read, so I need advice is fairly simple language, sorry. 1)Am I on the right track? (ie have I designed the tables the best way) 2)Do I need any more tables for the relationships? Thanks for any help Anne |
#6
|
|||
|
|||
Design help for community group database
The mentor/mentee suggestion was something I assumed you needed to do, from
your original post. If you are only tracking the leaders, then recording who is mentoring whom is probably irrelevant. Also, if you are only tracking leaders, perhaps it is only the training of leaders you are interested in. Similarly, if you are only tracking leaders, then it's only donations from leaders and payments authorised by leaders that you are interested in. Hopefully the information about enrolling leaders in an instance of a training course (not the course itself) was useful. -- 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 ... Thanks Allen for your suggestions. I have checked with others but most of them seem to be using a variety of spreadsheets which aren't quite in a format that I would like. I also thought that a database would be a more efficient way of keeping the records. Our new state website (NSW) has a database and prior to its launch earlier this year, we thought that we would be able to do all our Region record-keeping using it, online. Unfortunately this is not possible at this time and in the interim we are required to keep local records, but I don't wish to use the inherited paper-based system. I only have to keep records for the leaders and I won't be needing to enter camping/event details (other than training courses) and donations/payments. I don't quite understand your suggestion for the Training Table, especially "MentorID Number relates to Person.PersonID. Who does mentoring MenteeID Numbewr also relates to ". Who is mentored." and how to implement it (sorry to be so obtuse, but I really am only a novice), but I will play around with it to see if I can work it out. Do I assume that I don't need the Guiding Partner table and that the fields mentioned above go into the training table? Thanks again for your help Anne "Allen Browne" wrote: Anne, there's probably a fair bit more to it than the tables you posted. You might like to ask around the other guide troops and see if there is something else in place that they use, to save re-inventing the wheel. If you do want to build something from scratch, I would suggest using one table for all people (leaders and members.) This will make foreign keys much easier (e.g. where you need to book leaders and members into an event/camp, or where you need to accept donations/payments from people.) If you want to retain a person's training history (who mentored whom when), the table would then have fields like this: ID AutoNumber primary key MentorID Number relates to Person.PersonID. Who does mentoring MenteeID Numbewr also relates to ". Who is mentored. StartDate Date/Time when the mentor started with this mentee. EndDate Date/Time when this ceased. (Blank if current.) Comment Text or Memo Regarding the training courses, one training course is offered multiple times over the years, and people enrol in one instance of the training course (e.g. the one starting in Nov 2008), not in the course itself. Depending on what you need, you may need to have a table of the actual dates that make up the instance of the training course, attendance records for students for each date, and so on. "AnneS" wrote in message ... I am trying to set up a database to track Girl Guide leaders in my Region along with their training and awards history. Each leader has another leader as a Guiding Partner (or mentor) and this has to be reflected in their training history. Each leader's qualifications need to be "appraised" every 3 years and I need to be able to track when that is due. I also need to be able to get reports such as which leaders have certains training qualifications (modules) etc. Would appreciate any help. So far I have set up the following tables: LEADERS TABLE: MemberID (using Girl Guides membership number, not autonumber) primary key First Name Last Name District Division Region Position Home Phone Work Phone Mobile Address City State Postcode QUALIFICATION TABLE: QualID (autonumber) primary key Module Type Start Date Completion Date Expiry Date TRAINING COURSES TABLE TrainingID (autonumber) primary key Course Name Date attended AWARDS TABLE: AwardsID (autonumber) Primary key Award Type Date Received GUIDING PARTNERS TABLE GuidingPartnerID (autonumber) Primary key This is as far as I have gone as I haven't yet got my head around how to do the relationships, particularly in regards to foreign keys and object dependancies. I understand the concept of foreign keys, but not sure how to apply them with my scenario. The examples I have seen only have 3 tables so it is fairly simple to apply them. I have only a very basic knowledge of databases and I am getting confused by all the "techy" talk in the various online tutorials I have read, so I need advice is fairly simple language, sorry. 1)Am I on the right track? (ie have I designed the tables the best way) 2)Do I need any more tables for the relationships? Thanks for any help Anne |
#7
|
|||
|
|||
Design help for community group database
Sorry, Allen, I obviously haven't explained my requirements clearly enough
or more to the point I haven't understood your suggestion properly. I thought when you mentioned members that you were referring to members of the organisation who aren't necessarily leaders, but as I don't have to keep records for them I wrote my reply to you accordingly. 1) Yes I do need the mentor/mentee. So I need to know how to include that in the database. I didn't quite understand your explanation of how to achieve it though. I would be grateful if you would explain it again for me. As I said I'm sorry for being so dense. Which table do I have to place the mentor/mentee fields and the "relates to 2) Yes I am interested in the training of the leaders...but the training is separate to the mentoring. The training is conducted by the state organisation, whereas the mentoring is done in the Region by fellow leaders. The leaders are required to have a Guiding Partner (mentor) as they work through their modules. So the name of the Guiding Partner is tied in with modules (qualification table) rather than with the training courses. Obviously the complicating factor is that each leader can also be a Guiding Partner, but not all leaders are. 3) I don't have to keep any financial records in this database, so don't have to allow for any donations or payments. 3) I understand what you are saying about the training courses being held on several occasions and the leaders only attending one of those. Getting that sort of list might not be possible, so could I achieve the same effect by having a course name field and a date attended field? 4) How can I trigger an "alert" for when a leader's appraisal is due (required every 3 years by to maintain their qualification). From my reading of the help file, I will have to set many-many relationships. Is that correct? If so, how many extra tables will I need to include if any? Thanks again, Anne "Allen Browne" wrote: The mentor/mentee suggestion was something I assumed you needed to do, from your original post. If you are only tracking the leaders, then recording who is mentoring whom is probably irrelevant. Also, if you are only tracking leaders, perhaps it is only the training of leaders you are interested in. Similarly, if you are only tracking leaders, then it's only donations from leaders and payments authorised by leaders that you are interested in. Hopefully the information about enrolling leaders in an instance of a training course (not the course itself) was useful. -- 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. |
#8
|
|||
|
|||
Design help for community group database
Responses in-line.
-- 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 ... 1) Yes I do need the mentor/mentee. So I need to know how to include that in the database. I didn't quite understand your explanation of how to achieve it though. I would be grateful if you would explain it again for me. As I said I'm sorry for being so dense. Which table do I have to place the mentor/mentee fields and the "relates to The suggestion is to put everyone -- leaders, guides, mentors, mentees -- into the one table. In the long-term, this will make sense anyway: I suspect some of the leaders may have been guides in their own day. This table might be called tblPerson, and will have an AutoNumber field named (say) PersonID. Now you need another table - tblMentor - to track who is mentoring whom, with fields as shown previously. In your Person table, perhaps Anne S is person 24, and you are mentoring Jenny Fisher (PersonID 87) and Juliette Jones (PersonID 93) as of the start of this year. The records would look like this: ID MentorID MenteeID StartDate EndDate 1 24 87 1/1/2008 2 24 93 1/1/2008 At the end of this year, you stop mentoring Jenny, so you enter 31/12/2008 as the end date. Next year Judy Dench (PersonID 55) will mentor Jenny, so you add a new record to show this: 3 55 87 1/1/2008 You still have the old record showing that you mentored Jenny for 2008, and so you have a complete history of who mentored whom when. If one guide can only ever have 1 mentor at at time, and you don't need a complete history, then you could get away with just adding a MentorID field to tblPerson instead. This field would hold the PersonID value of the person who is their current mentor, so Jenny's record would have 24 in the field this year, and 55 in the field next year. 2) Yes I am interested in the training of the leaders...but the training is separate to the mentoring. The training is conducted by the state organisation, whereas the mentoring is done in the Region by fellow leaders. The leaders are required to have a Guiding Partner (mentor) as they work through their modules. So the name of the Guiding Partner is tied in with modules (qualification table) rather than with the training courses. Obviously the complicating factor is that each leader can also be a Guiding Partner, but not all leaders are. This is essentially the same thing. The only change would be that you might want to add a MentorTypeID field to tblMentor if you need to distinguish whether the mentoring is leader-to-guide or partner-to-leader. 3) I don't have to keep any financial records in this database, so don't have to allow for any donations or payments. Good. 3) I understand what you are saying about the training courses being held on several occasions and the leaders only attending one of those. Getting that sort of list might not be possible, so could I achieve the same effect by having a course name field and a date attended field? You can do that. It depends what you need to report. It might be more water-tight to record everyone who did a course together as a group, but perhaps you don't need to worry about that. 4) How can I trigger an "alert" for when a leader's appraisal is due (required every 3 years by to maintain their qualification). Is this the only thing that ever expires? Or might there be other things as well, such as a St John's medical certificate that needs periodic renewing? I suspect you will need a little table listing the courses/certificates people could take. This table will have a pair of fields that together indicate how often it needs renewing (or is left blank if they never need renewing.) The fields are a number and a text field that indicates a period. The period is an expression you can use in DateAdd, e.g. "d" for days, "m" for months, "yyyy" for years. So the data might look like this: CourseID CourseName Freq PeriodID 1 Leader Appraise 3 yyyy 2 St John's Medical 6 m Now you need another table to record when the person actually had one of these, e.g.: ID PersonID CourseID CourseDate 1 24 1 1/1/2005 2 24 1 1/1/2008 3 24 2 1/7/2008 You can then create a query using both tables. Make it a totals query, to group by PersonID and CourseID and get Max of CourseDate. You can then calculate the date they are due again as: DateAdd([PeriodID], [Freq], MaxOfCourseDate) (Actually, there's a bit more to it than that if you want to be notified that someone has *never* done a course that they should have.) HTH. |
#9
|
|||
|
|||
Design help for community group database
Allen, my responses
1) Everyone in same table... As each leader can be a leader, mentor (ie Guiding Partner), or mentee how should I enter them in the person (ie Leaders) table? Just the one entry per person I assume? 2) Because leaders can be working on more than one module at a time they will often have more than one Guiding Partner at a time. I also require the full history 3) Is there any problem with my naming the tables in line with Girl Guide jargon, or should I be following a naming protocol? 4) "It might be more water-tight to record everyone who did a course together" Leaders attend training courses with leaders from all over the state, but I only need to track those from my Region, so the simple course name and date attended should be sufficient for my needs. 5) THE THINGS THAT EXPI i) Main qualification: every 3 years ii) Senior First Aid: every 3 years iii) Camping qualifications: 3 years from date of last camp iv) Certain completed modules: every 3 years 6) Just to make certain that I have understood you correctly, this is where I am up to with the table design. Are they correct and if not what other fields do they need? tblLeaders LeaderID (autonumber) Primary key Membership No key First Name Last Name District Division Region Position Home Phone Work Phone Mobile Address City State Postcode Husband/Partner name   tblGuidingPartners GPID (autonumber) Primary Key MentorID MenteeID Start Date Endate tblModules ModuleID (autonumber) Primary Key Freq PeriodID tblModulesCompleted QualID (autonumber) Primary key MentorID MenteeID ModuleID StartDate EndDate tblTrainingCourses CourseID (autonumber) Primary Key Course Name Freq PeriodID tblTrainingAttended TrainingID (autonumber) Course Name Date attended tblAwards AwardID (autonumber) Primary key Award Name Date Received 7) For the relationships I understand that I will need junction tables so that I can set the many-many relationships. I have the following tblModule Details: ModuleID QualID and tblTraining CourseID TrainingID Are these correct? Do I need anymore and how do I organise other relationships? Thanks you for your patience. Anne "Allen Browne" wrote: The suggestion is to put everyone -- leaders, guides, mentors, mentees -- into the one table. In the long-term, this will make sense anyway: I suspect some of the leaders may have been guides in their own day. This table might be called tblPerson, and will have an AutoNumber field named (say) PersonID. Now you need another table - tblMentor - to track who is mentoring whom, with fields as shown previously. In your Person table, perhaps Anne S is person 24, and you are mentoring Jenny Fisher (PersonID 87) and Juliette Jones (PersonID 93) as of the start of this year. The records would look like this: ID MentorID MenteeID StartDate EndDate 1 24 87 1/1/2008 2 24 93 1/1/2008 At the end of this year, you stop mentoring Jenny, so you enter 31/12/2008 as the end date. Next year Judy Dench (PersonID 55) will mentor Jenny, so you add a new record to show this: 3 55 87 1/1/2008 You still have the old record showing that you mentored Jenny for 2008, and so you have a complete history of who mentored whom when. If one guide can only ever have 1 mentor at at time, and you don't need a complete history, then you could get away with just adding a MentorID field to tblPerson instead. This field would hold the PersonID value of the person who is their current mentor, so Jenny's record would have 24 in the field this year, and 55 in the field next year. This is essentially the same thing. The only change would be that you might want to add a MentorTypeID field to tblMentor if you need to distinguish whether the mentoring is leader-to-guide or partner-to-leader. You can do that. It depends what you need to report. It might be more water-tight to record everyone who did a course together as a group, but perhaps you don't need to worry about that. Is this the only thing that ever expires? Or might there be other things as well, such as a St John's medical certificate that needs periodic renewing? I suspect you will need a little table listing the courses/certificates people could take. This table will have a pair of fields that together indicate how often it needs renewing (or is left blank if they never need renewing.) The fields are a number and a text field that indicates a period. The period is an expression you can use in DateAdd, e.g. "d" for days, "m" for months, "yyyy" for years. So the data might look like this: CourseID CourseName Freq PeriodID 1 Leader Appraise 3 yyyy 2 St John's Medical 6 m Now you need another table to record when the person actually had one of these, e.g.: ID PersonID CourseID CourseDate 1 24 1 1/1/2005 2 24 1 1/1/2008 3 24 2 1/7/2008 You can then create a query using both tables. Make it a totals query, to group by PersonID and CourseID and get Max of CourseDate. You can then calculate the date they are due again as: DateAdd([PeriodID], [Freq], MaxOfCourseDate) (Actually, there's a bit more to it than that if you want to be notified that someone has *never* done a course that they should have.) HTH. |
#10
|
|||
|
|||
Design help for community group database
Re #1: Yes: each person should be entered once only. If a person has several
roles, you will have a related table to enter the roles. Re #2: Then it will be a related table just like the mentor/mentee table. Re #3: Use names that are meaningful to the Guides. Avoid spaces and strange characters (like #) in names. (For example FirstName rather than First Name. This will save you having to add square brackets everywhere.) A-Z, 0-9, and underscore are safe characters. Avoid reserved names like Name, Date, Select, Order, etc. Here's a list to refer to if you are unsure about whether a name is okay: http://allenbrowne.com/Ap****ueBadWord.html For example, POSITION is a reserved word. I suspect tblModule will need a ModuleName field (so you know what it's called.) I think you are putting everyone into tblLeaders, whether they are mentors or mentees or whatever. In tblModulesCompleted, I didn't understand why you have both MentorID and MenteeID. I would expect just a LeaderID field here, unless the courses are designed to be done by people repeatedly each time they are paired up with someone. tblAwards contains a list of the awards, so I don't think it has a DateReceived field. I imagine you would need another table where you record who was awarded what: tblAwardReceived: - AwardReceived autonumber - AwardID number what award was given - LeaderID number who received it. - DateReceived date/time when they received it. If this whole thing about related tables is new, a basic example explanation might help: http://allenbrowne.com/casu-06.html -- 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 news Allen, my responses 1) Everyone in same table... As each leader can be a leader, mentor (ie Guiding Partner), or mentee how should I enter them in the person (ie Leaders) table? Just the one entry per person I assume? 2) Because leaders can be working on more than one module at a time they will often have more than one Guiding Partner at a time. I also require the full history 3) Is there any problem with my naming the tables in line with Girl Guide jargon, or should I be following a naming protocol? 4) "It might be more water-tight to record everyone who did a course together" Leaders attend training courses with leaders from all over the state, but I only need to track those from my Region, so the simple course name and date attended should be sufficient for my needs. 5) THE THINGS THAT EXPI i) Main qualification: every 3 years ii) Senior First Aid: every 3 years iii) Camping qualifications: 3 years from date of last camp iv) Certain completed modules: every 3 years 6) Just to make certain that I have understood you correctly, this is where I am up to with the table design. Are they correct and if not what other fields do they need? tblLeaders LeaderID (autonumber) Primary key Membership No key First Name Last Name District Division Region Position Home Phone Work Phone Mobile Address City State Postcode Husband/Partner name tblGuidingPartners GPID (autonumber) Primary Key MentorID MenteeID Start Date Endate tblModules ModuleID (autonumber) Primary Key Freq PeriodID tblModulesCompleted QualID (autonumber) Primary key MentorID MenteeID ModuleID StartDate EndDate tblTrainingCourses CourseID (autonumber) Primary Key Course Name Freq PeriodID tblTrainingAttended TrainingID (autonumber) Course Name Date attended tblAwards AwardID (autonumber) Primary key Award Name Date Received 7) For the relationships I understand that I will need junction tables so that I can set the many-many relationships. I have the following tblModule Details: ModuleID QualID and tblTraining CourseID TrainingID Are these correct? Do I need anymore and how do I organise other relationships? Thanks you for your patience. Anne "Allen Browne" wrote: The suggestion is to put everyone -- leaders, guides, mentors, mentees -- into the one table. In the long-term, this will make sense anyway: I suspect some of the leaders may have been guides in their own day. This table might be called tblPerson, and will have an AutoNumber field named (say) PersonID. Now you need another table - tblMentor - to track who is mentoring whom, with fields as shown previously. In your Person table, perhaps Anne S is person 24, and you are mentoring Jenny Fisher (PersonID 87) and Juliette Jones (PersonID 93) as of the start of this year. The records would look like this: ID MentorID MenteeID StartDate EndDate 1 24 87 1/1/2008 2 24 93 1/1/2008 At the end of this year, you stop mentoring Jenny, so you enter 31/12/2008 as the end date. Next year Judy Dench (PersonID 55) will mentor Jenny, so you add a new record to show this: 3 55 87 1/1/2008 You still have the old record showing that you mentored Jenny for 2008, and so you have a complete history of who mentored whom when. If one guide can only ever have 1 mentor at at time, and you don't need a complete history, then you could get away with just adding a MentorID field to tblPerson instead. This field would hold the PersonID value of the person who is their current mentor, so Jenny's record would have 24 in the field this year, and 55 in the field next year. This is essentially the same thing. The only change would be that you might want to add a MentorTypeID field to tblMentor if you need to distinguish whether the mentoring is leader-to-guide or partner-to-leader. You can do that. It depends what you need to report. It might be more water-tight to record everyone who did a course together as a group, but perhaps you don't need to worry about that. Is this the only thing that ever expires? Or might there be other things as well, such as a St John's medical certificate that needs periodic renewing? I suspect you will need a little table listing the courses/certificates people could take. This table will have a pair of fields that together indicate how often it needs renewing (or is left blank if they never need renewing.) The fields are a number and a text field that indicates a period. The period is an expression you can use in DateAdd, e.g. "d" for days, "m" for months, "yyyy" for years. So the data might look like this: CourseID CourseName Freq PeriodID 1 Leader Appraise 3 yyyy 2 St John's Medical 6 m Now you need another table to record when the person actually had one of these, e.g.: ID PersonID CourseID CourseDate 1 24 1 1/1/2005 2 24 1 1/1/2008 3 24 2 1/7/2008 You can then create a query using both tables. Make it a totals query, to group by PersonID and CourseID and get Max of CourseDate. You can then calculate the date they are due again as: DateAdd([PeriodID], [Freq], MaxOfCourseDate) (Actually, there's a bit more to it than that if you want to be notified that someone has *never* done a course that they should have.) HTH. |
Thread Tools | |
Display Modes | |
|
|