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
Email
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.