Included databases

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to design a database for a school that includes information connecting
parents, students, grandparents, step parents, guardians, people allowed to
pick up child. I will also need to track volunteers, who can paint, work
fundraisers, etc. Does Microsoft Access have a school database already
designed? I saw a contact database but it was not detailed enough for what I
need.

Thanks,
Marie
 
Marie, I am not aware of any existing database templates available from
Microsoft that will give you want you want.

To give you the flexibility you need, the key element will be to put all the
people into one table: parents, students, volunteers, guardians, etc.

Then create another table (or tables) that define the relationships between
people, and their roles in those relationships. The fields might be:
ChildID foreign key to Person table.
CarerID foreign key to Person table.
RoleID "parent", "guardian", "nanny", etc.
StartDate date this carer began this role for this child
EndDate date carer ceased this role for this child. Blank if current.

That allows a child to have many carers, and a carer to have many children.
The dates let you keep historical records (so you know why you released a
child to a foster parent on a date last year.)

You would need another table to handle the Volunteer info:
PersonID who volunteered
JobID what they are available for
StartDate when they first volunteered
EndDate when they are no longer available

If you also have to handle committees, mailing lists, and so on, this
example might help:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
 
That was very, very helpful. So each family member would be a separate
record? How would I get them combined in a report, e.g., put something like
Tim and Mary Hanson on a record where the Tim is in one recrod and Mary is in
another record?

Marie
 
Allen, that was very, very helpful. So each family member would be a separate
record? How would I get them combined in a report, e.g., put something like
Tim and Mary Hanson on a mailing label or a class roster of parents where the
Tim is in one recrod and Mary is in another record?
Marie
 
Yes, Tim and Mary should have their own records, I think.

Re identifying who belongs to a household, take a look at the link. The fun
part is desiging something that is flexible enough to cope with anything.
For example, someone's Mum and their Dad are not necessarily members of the
same household. Your database has to cope when a marriage breaks up, and Mum
marries someone else who has 2 more children, or when little Jimmy is a
member of 2 housholds (half his time with Mum, and half with Dad.) The
suggested structure lets you handle those kinds of cases.
 
You really understand what I am after because you are right about all the
possibilities. Will all the ID fields be autonumber? If so, how do I keep
from looking up that autonumber to see what the id for the family is as I
enter additional members? I would have thought there would have been a
familyID field?
Marie
 
The sample database in the link does use AutoNumbers as the primary key for
the tables, but you don't have to show those autonumbers on your form. If
you want to ensure people can't look that up, you will have to design the
interface so the user cannot easily go to the table, which is normally how
we design Access applications, i..e everything the user does happens in
forms and reports.

Each family will have a family name that identifies them. The person who
enters family members would need to have the permissions to see the members
of the family.

In the suggested structure, each family is a "client" in their own right, as
well as the individuals. That make it possible to send a mailing to "The
Smith Family", or to an individual in the family, since they are all in the
same table. (The IsCorporate field (yes/no) distinguishes corporate entities
from individuals.)

We did not simply put a FamilyID field into the Person table, because that
would not cope with the child who spends half their time with Mum and half
with Dad.

Hope that's not too confusing.
 
Back
Top