Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Included databases

Discussion in 'Information Technology' started by Serendipity, Jul 28, 2006.

  1. Serendipity

    Serendipity
    Expand Collapse
    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
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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

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

    "Serendipity" <Serendipity@discussions.microsoft.com> wrote in message
    news:8FAE9587-F918-4157-99F8-747E1011EB50@microsoft.com...
    >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
     
  4. Serendipity

    Serendipity
    Expand Collapse
    Guest

    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 Browne" wrote:

    > 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
    >
    > --
    > 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.
    >
    > "Serendipity" <Serendipity@discussions.microsoft.com> wrote in message
    > news:8FAE9587-F918-4157-99F8-747E1011EB50@microsoft.com...
    > >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

    >
    >
    >
     
  5. Serendipity

    Serendipity
    Expand Collapse
    Guest

    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


    "Allen Browne" wrote:

    > 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
    >
    > --
    > 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.
    >
    > "Serendipity" <Serendipity@discussions.microsoft.com> wrote in message
    > news:8FAE9587-F918-4157-99F8-747E1011EB50@microsoft.com...
    > >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

    >
    >
    >
     
  6. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.

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

    "Serendipity" <Serendipity@discussions.microsoft.com> wrote in message
    news:C5E6BB0F-3AF5-47FB-9E4B-3BFC25204779@microsoft.com...
    > 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
    >
    >
    > "Allen Browne" wrote:
    >
    >> 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
    >>
    >> "Serendipity" <Serendipity@discussions.microsoft.com> wrote in message
    >> news:8FAE9587-F918-4157-99F8-747E1011EB50@microsoft.com...
    >> >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.
     
  7. Serendipity

    Serendipity
    Expand Collapse
    Guest

    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

    "Allen Browne" wrote:

    > 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.
    >
    > --
    > 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.
    >
    > "Serendipity" <Serendipity@discussions.microsoft.com> wrote in message
    > news:C5E6BB0F-3AF5-47FB-9E4B-3BFC25204779@microsoft.com...
    > > 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
    > >
    > >
    > > "Allen Browne" wrote:
    > >
    > >> 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
    > >>
    > >> "Serendipity" <Serendipity@discussions.microsoft.com> wrote in message
    > >> news:8FAE9587-F918-4157-99F8-747E1011EB50@microsoft.com...
    > >> >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.

    >
    >
    >
     
  8. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.

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

    "Serendipity" <Serendipity@discussions.microsoft.com> wrote in message
    news:48504D00-8BF2-4DCC-B5C5-E7BFE9A79976@microsoft.com...
    > 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
    >
    > "Allen Browne" wrote:
    >
    >> 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.
    >>
    >> --
    >> 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.
    >>
    >> "Serendipity" <Serendipity@discussions.microsoft.com> wrote in message
    >> news:C5E6BB0F-3AF5-47FB-9E4B-3BFC25204779@microsoft.com...
    >> > 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
    >> >
    >> >
    >> > "Allen Browne" wrote:
    >> >
    >> >> 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
    >> >>
    >> >> "Serendipity" <Serendipity@discussions.microsoft.com> wrote in message
    >> >> news:8FAE9587-F918-4157-99F8-747E1011EB50@microsoft.com...
    >> >> >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.
     

Share This Page