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.