Relationships and queries for mailings

[Guest]

I am currently working on a database for the Youth & Community Centre where I
work. I need it to do be used for quite a few things but don't really
remember how to go about it as this has been a while for me.

At the moment, I have five tables:
- Members
- Guardians
- Carers
- Social Workers
- Partners

Some of members are siblings. When sending updates to parents, I only want
to send one mailing piece to each household. So far my attempts to set up
relationships and queries have failed.

Thank you in advance for the help.

NitaMax

 

[Guest]

Have one table for people - period. Use an autonumber as primary key. Have
two other fields besides all the other home phone, cell phone, work phone,
fax, pager, etc., stuff to show relations.
The first field shows which student they are related to and the second what
the relationship is - Dad, Mom, StepDad, StepMom, All the granddads,
gradmothers, stepgranddads, stepgrandmothers.
In the Relationship window insert the same student table twice - the second
will have a suffix of _1 hung on it. Drag the primary key filed from the
first to the student relation field. Set referential integrity on.

First you will add the student then their relatives.

Use a form and a subform for student and relatives. There will be no need
to lookup the autonumber as the one-to-many relation will auto fill it in for
you if you have the Master/Child links for the form/subform set on the
autonumber primary key field to the relative field.

 

[Pat Hartman\(MVP\)]

This needs to be a many-to-many relationship rather than a 1-many. That
means that you need a relation table. It needs three columns:

StudentID (pk fld1, foreign key to tblPeople)
RelatedID (pk fld2, foreign key to tblPeople)
RoleID (pk fld2, foreign key to tblRoles - mother, father, etc.)