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