How to show relationships in Single Table?

[Johhhn]

OK, hopefully I can explain this well enough!

Here's the database info:

Database holds 'People'.

One table.

People that are 'related' to each other share a 'Family ID' so you can track relationships that way.

So, we have student Bobby, father Bob, and mother Betty. They all have a unique ID, but share the 'Family ID'. So, when you are looking at Bobby, or Bob for that matter, you will see a related table that shows everyone else that has the same Family ID.

So, one table, using a Family ID, to show the same people in that TO that are 'related' to each other.

I didn't want to do a child Table and then another Table for other people because, ultimately, the database holds any 'type' of people and they can relate to each other using the Family IDs.

OK, so here's the problem where I need help:

There is a 'Mail to this person' field, where, usually one of the parents, anybody can get checked. So in a 4 person 'family', let's say Betty gets the checkbox. So, we can do a search for 'Mail to this person' and we know that everyone that has that checked should get a Letter, etc..

So, here's the dilemma, let's say we are sending a letter out thanking the parent(s) for their patronage and we want to mention their kid(s), how do we do that?

There is also a 'relationship' field, where each 'kid' that is 'important' is assigned 'Self' and the parent, or aunt, uncle, is assigned 'aunt' or 'uncle' or 'mother' or 'father', etc. The kid is always assigned 'self' unless that kid is not 'important', ie, critical to the 'relationships' for this purpose in the database.

So, I can setup a form letter thanking the Parent, but I also want to say something about any of their kids that have the 'relationship' field assigned 'self'. How would I do this for an 'infinite' amount of children possible?

I hope I am clear on all of this :

thanks for any insights!

[Fitch]

Make a stored calc field (or auto-enter) that = "self"

Make a relationship based on both that field to the 'relationship' field and on family ID.

List(ImportantKids)

[Johhhn]

thanks for your help!

I was halfway there with the TO the way you set it up, but didn't think to use the List function to display. I've been playing with the List function as well,, soooo, now, here's the million $ question--

How do I do this?

Here's the output I hope to have:

Dear Juan and Juanita Smith,

blah blah blah

blah blah blah

Here is a list of your kids and the classes they attend:

Juanito: 1st grade

Juanita: 2nd grade

Paco: 5th grade arts, 5th grade math, 5th grade english.

sincerely,

John Doe, head admin

---------

Classes is a separate table joined by a People ID.

How can I parse the data of the list() and at the same time slap in data from a related Classes table?

And also, if I need to show some information for each child in the Letter to the Parent,, such as DOB, Gender, etc., how can I do that in a Letter?

Using the list function I can see how I can show all of their kids, but how can I 'grab' data along with each child- whether it's from the same Table or a related one?

thanks a bunch people!!

[David Jondreau]

By using List() in the Kids table too. You need a calc field in the Kids table that pulls the info from their classes. Something like:

Kids::Name & "DOB " & DOB ": " & Substitute( List(Kids_Classes::Class; ¶; ", "))

Then base your List() in the Parent table off that field.

[Johhhn]

Awesome!

thanks!

I was up till 3am last night busting my brain and today I came up with a calculated field to put into the 'kids' table to show their classes, but wasn't sure how to list it 'nicely' and of course, Substitute the return carriage!! thanks!!!!

You guys are great!

Now,,, to implement and see what else comes up :