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