A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Design help for community group database



 
 
Thread Tools Display Modes
  #1  
Old October 31st, 2008, 04:47 AM posted to microsoft.public.access.tablesdbdesign
AnneS
external usenet poster
 
Posts: 9
Default 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
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

  #2  
Old October 31st, 2008, 06:21 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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
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


  #3  
Old October 31st, 2008, 12:37 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default 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  
Old November 1st, 2008, 05:37 AM posted to microsoft.public.access.tablesdbdesign
AnneS
external usenet poster
 
Posts: 9
Default 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  
Old November 1st, 2008, 05:56 AM posted to microsoft.public.access.tablesdbdesign
AnneS
external usenet poster
 
Posts: 9
Default 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
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



  #6  
Old November 1st, 2008, 09:29 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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
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




  #7  
Old November 1st, 2008, 12:20 PM posted to microsoft.public.access.tablesdbdesign
AnneS
external usenet poster
 
Posts: 9
Default 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  
Old November 1st, 2008, 12:55 PM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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  
Old November 3rd, 2008, 08:02 AM posted to microsoft.public.access.tablesdbdesign
AnneS
external usenet poster
 
Posts: 9
Default 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
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.

  #10  
Old November 3rd, 2008, 08:31 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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
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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:28 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.