Need help with hyper-Genealogy...

[Sean O'Halloran]

I'm a social worker for the state of Maryland, pushing hard to build useful software for my fellow Child Protective Services workers. My partner in this is a great programmer who is overloaded with trying to keep the agency's 90 plus Access 2K up and running - and he can't devote much research time to my wacky ideas.

So I need advice / opinions on how to build the following, related to creating and storing data on the members of families we serve: workers need to record data on individuals, and relate that data to other individuals in various ways.

For example, I might investigate a family comprised of a mother, her current paramour, her three biological children, and the paramour's child. I need to enter each individual's data, and be able to recall that set of data as a "Current Household". I would also gather data on the biological father (or, often, FATHERS) of the mother's three children, and would need to relate each child to their biological parents as "Biological Family".

NEXT - am I boring you yet? - I want the db to generate / derive certain relationships between the children based on parental information; full siblings, half-siblings, step-siblings and adopted siblings.

I want the db to generate these relationships because I don't want the workers to have to set each relationship individually; in the example above with 7 people it would take, what, 36 separate entries to set all the various relationships?

This function is important not only because we need the relationship data on reports. If children end up in foster care, or if they are placed for adoption, we need to collect medical and family history for each child - history that usually relates to the biological parents. Since each child MUST have a separate case record, we currently end up entering the same data over and over.

With a "relationship function" the worker could enter mother's data, father1 data, father2 data, etc., and have it appear in the proper records and reports.

I know I'm presenting multiple issues - basically I'm crying for help - and perhaps I'm asking too much, too vaguely. But any assistance would be greatly appreciated by myself, 200 workers in Baltimore County Maryland, and thousands of children we try to serve and protect. Thanks - Sean

 

[raskew]

Sean-
While it's true that it's possible to do the things you're asking in Access, there is so much genealogy software on the market that it would probably be more productive to purchase a copy of FamilyTreeMaker or a competing product and go from there.
There is Access shareware available that does genealogy based stuff (with varying degrees of success). You might want to do some searches and download trial copies.
If economics is a factor (cost of established application vs programming time) you're almost sure to be better off with an established application.

Bob

 

[Sean O'Halloran]

Thank you for your response, but unless I'm missing something in the genealogy programs I've researched, they don't provide the functionality we need. Most of them depend on setting biological relationships, and finding common bio-ancestors to create those relationships, and they do not allow for conditions such as temporary paramours, same-sex partners, and the automatic derivation of relationships such as sibling, aunt/uncle, grandparent, etc.

The shareware ones I've seen - and I include several genogram drawing programs that create excellent family diagrams - force you along the narrow path of creating parent-to-child relationships, which means you end up with only biological family groups, not the real-world groupings we encounter in our social work.

I am most appreciative of your interest, and welcome any other ideas - and, despite my previous comments, I will look again at Family Tree Maker. Money is NOT a factor - because the State won't give us any - and we are doing this in our spare time.

Perhaps I should add that my computer guru and I have created three extensive db's that already capture all the case data - we're just trying to add this functionality to an existing db. I may not know enough to even define the issues properly, but I think Victor (computer guru) will understand whatever suggestions you have, even if I don't.

My though is that we need a Relationship table that will have the following basic structure...

RelationshipPrimaryKey
Case Associate 1 ID number
Relationship Code
Case Associate 2 ID number

...where the relationship code specifies the type of relationship. My thought is that the db would search and compare all the Case Associate records to find certain patterns, like:

If:
Mary is the mother of John, and
Daniel is the brother of Mary,
Then:
Daniel is the uncle of John, and
John is the nephew of Daniel

The first two statements would be entered by the user, and the last two would be derived by an...update query? Code?...and added to the respective case associates relationship records.

Thanks again - Sean

 

[neileg]

I have a db to generate cat pedigrees. I hold all the cats in one table and in each record I have a field for the father and the mother. These contain the key field for the related cats. When I generate the pedigree, I use a query that has the table replicated the relevant number of times with the relationship mapped from the father field to the key field of the next table, and the father of that to the key field of the next table. Similarly for the mothers. The realtionship diagram is just like a family tree. (I hope you understand what I mean).

I don't see why you can't have a similar set up with more than just two related records. The problem is that this gives you a fixed number of relations, and as I have it set up, the relations have to be populated. To give five generations I need to have 31 instances of the table. I guess it depends what you want to achieve.

Perhaps the above is just mindless wanderings of no earthly use, but maybe it will help?

 

[The_Doc_Man]

Whenever I see a problem like this, I have to pull out my "model-maker" speech. Excuse me if this gets long-winded.

Access is a business modeling tool. Other than simple math functions, you have no inherent ability with Access other than establishing relationships and code. What you need to do your job is to have a working model of what you want Access to do for you. If you cannot write something on a sheet of paper then Access won't help you write a program.

Therefore, you need to draw out a relationships model and analyze it for the factors you need to consider. Having said that, I'll ruminate on at least a STARTING point.

You have at least two distinct entities to consider: People and Households. Therefore, your model must somehow define this fact. Relationships among the people are varied and ephemeral, so you have to consider this fact as well.

First, you are going to have to LIST all of the possible relationships that a child may have to the previous generation: Birth mother; natural mother (in the case of in vitro fertilization); natural father; step mother; step father; foster mother; foster father; adopted mother; adopted father; male legal but non-foster, non-adoptive guardian; female guardian (same as prior for male...); mother's-current-significant-other who is not related to the child; ditto for father, etc. etc.

Next, realize that except for Birth mother, natural mother, and natural father, ALL of the other relationships need time-tags on them for potential start/stop times (where blank for stop-time means "current").

OK, now here is where it gets tricky. You need more relations to be defined in a dynamic relation table where you are dealing with child-relations that are "sibling" variants: full, half, adoptive, foster, cohabitant, whatever else you wish to define.

Are you with me so far? The point I'm making is that if you don't start with a list of possible relationships, you'll never write anything that manages said relationships.

Back to the problem at hand:

You will need at least several tables. Here are some examples:

tblPerson
- personID - prime key - perhaps as simple as an autonumber
- person name info
- other identifying info related permanently to that person (like social security number, eye color, race, scars, stuff like that)

tblParentage
- child's personID
- parent-like person's personID
- code for relationship of child to parent-like person
- date on which that relation started
- date on which that relation ended (or 0 if current)

tblSiblings
- child-of-interest's personID
- other-child's personID
- code for relationship of child-of-interest to other-child
- date on which that relation started
- date on which that relation ended (or 0 if current)

We still have not dealt with HouseHolds, where I am going to take the loose definition that when two or more adults live together with zero or more children, you have a household for your purposes.

tblHouseHold
- householdID (primary key) probably can be autonumber
- address (if any)
- nature of household: married, living-in-sin, etc.
- date on which household came into existence
- date on which household dissolved (or 0 if current)

tblHHMembers
- personID of member of the household
- householdID of household
- relationship to household: (born into it, adopted, foster, just happened to move in when parent moved in, etc. etc.)

Now, as you can clearly see, this is a helluva complex model. Until you can define on paper each and every relationship you wish to have on your printed report, you will be unable to get there from here.

Basically, what you will need to have from this point is to define a list of relationships and how they come into being. Then, when a household is formed, you have to enter its members. From that point, though, you can have queries attempt to create new relationship records based on the rules you define.

For instance:

Household # 15698 forms by marriage of personID #210444 and #195512. PersonID #210444 brings along her two natural children, personID #210445 and #210446. PersonID #195512 brings along his natural child #195513.

From that information, your business rules would create two new step-child relations between 210445 and 210446 and their stepdad 195512. Also between 195513 and 210444. Then, you can derive step-sib relations between 195513 and the two kids 210445 and 210446. If they have a new kid, personID 225166, you would have to enter two "natural child" relations for the parents and three new "half-sib" relations for the existing children.

Sounds complicated? You betcha, Red Ryder! The point is, you MUST define EVERY business rule of that type before you hope to implement those business rules. Can it be done? Yes. Will it be easy? Doubt it. Can it be done quickly? Don't bet the farm on it.

In summary, start by defining the entities you wish to track. I've named at least five entities: persons, households, and three types of relationships. No doubt there could be more. For instance, I haven't touched on caseworkers at all in this discussion. I'm sure that's good for at least two more tables:

tblCaseWorker and
tblCaseAssignments (which persons are assigned to which caseworkers)

Then you have to define the possible values associated with the various person-relationship tables: Parental, Sibling, and Membership in Household.

Then you have to make rules that take into account the time variability of Households and Memberships.

You also have to put in safeguards to prevent double-entry of relationships when you run a query. I.e. if two related people were in the database the last time you did a query, their relationships don't always need to be updated. But sometimes, maybe, they do.

Of course, you also have to recognize that every relationship can be turned around. For instance, to find everyone who has a parental relationship to a child, you group the Parental table by child. But to find every person who was some flavor of child for a given parental-type person, you group by the parental-person field. (And recognize in reports designed based on the turned-around queries that you have reversed the polarity of the relationship.)

Once you have all of this designed on paper, your programmer has a chance to implement this problem. But until you can write the rules for EVERY relationship you want to track, you won't make it. Particularly on a problem this massive.

 

[neileg]

The_Doc_Man wrote:

Access is a business modeling tool. Other than simple math functions, you have no inherent ability with Access other than establishing relationships and code. What you need to do your job is to have a working model of what you want Access to do for you. If you cannot write something on a sheet of paper then Access won't help you write a program.

I'm not sure I agree. I think Access is a personal productivity tool, and that is the way I use it. Sometimes the db grows where it becomes a business modelling tool, but not always. Sometimes, roughing a scenario out in Access can help in identifying the issues.

This is analogous to my use of Word. I can use this to write up my shopping list, and it might not become a Pullitzer prize winner. Excel works just fine at calculating 17.03% of 10,456.78, too.

 

[Sean O'Halloran]

This is to The Doc Man: Sorry about the direct e-mail. And just to reiterate - I really appreciate your thoughtful post. I've printed it out , and e-mailed it to my programmer.

Thanks again - and thank you for your other service to me and my family, too. Be careful out there. - Sean

 

[The_Doc_Man]

To Neil: I won't quibble. In the specific question as posed, Access will be a business modeling tool whether everyone else uses it that way or not.

To Sean: Don't hesitate to post further questions here, though I think you will understand (considering the e-mail that you mentioned) that sometimes I have higher priorities to attend to. When I find that I have ideas to share, I will certainly share them.

(In fact, some folks probably would accuse me of sharing ideas even when I really DON'T have ideas to share... )

 

[TLJ]

looking for animal pedigree db

I have a db to generate cat pedigrees. I hold all the cats in one table and in each record I have a field for the father and the mother.

to Neil:
I was planning on building a rabbit pedgree db. would you consider emailing yours to me.