Combining Husband and Wife names for newsletter name

[dayspring]

I have two tables. One is people and contains fields called first names, last names, etc. The second is a family table that contains family names. Everyone in the People table has a family and family number from the family table. In the People table there is also a field called "Family_Relationship" which has selections of Head, Spouse, Child, or Single. I need to create a calculated field that will give me the following:

For a record with Head in the Family_Relationship field - First name of the family head and first name of the Spouse and the last name (example: John and Jane Doe)
For a record with Spouse in the Family_Relationship field - Same as above
For a record with Child or Single in the Family_Relationship field - First and Last name (example:Julie Shaw)

My reason is for a newsletter name where I only send one copy to a couple and it would have both their names on it. Is there another way to do this if this is not possible?

[Raymond Lyons]

Re: Combining Husband and Wife names for newsletter name

As I look at this it's after midnight for me so I am not going to be able to give this much attention. However, at first blush I am confused by your set design, i.e., I am not sure what you are trying to do with it or even how you could reasonably do what I think you want. But:

1) In today's family situations a simple "John and Jane Doe" is wholly inadequate, i.e., tons of husbands have wives with different last names, and with step children many "children" of these couples even have a last name different from either their "father" or "mother" (biological or other wise). I would like to think you would want to be able to handle any of these situations.

2) If what you are doing with your database is very simple and doesn't involve a oodles of children, you might consider doing the whole thing as a single table with enough fields to cover a reasonable number of children. Would waste a lot of space but might make life very simple for you (i.e., your calculated filed would be very easy).

3) If a single table is unappealing, you might try a simpler set with just 2 tables, one with a ID field and maybe the 1st and last name of the head of the household and a 2nd table with the same ID field and 3 other fields: First name, Last name and relationship (spouse, child, etc.). The first table would be the parent in the set linked one to many with the 2nd table. With that arrangement I am sure you could build the calculated name field you are after.

4) Or, maybe someone else will have an even better idea.

Raymond Lyons

[dayspring]

Re: Combining Husband and Wife names for newsletter name

Raymond,
Thanks for your suggestions. Sorry I did not explain this all better. As I'm sure you understand, I am learning as I go so certainly open to other ways of doing this. The database I am working on is a replacement for a Lotus Approach dbase that I put together 18 years ago for our not for profit organization. It also includes a number of other tables that track donations, trips, volunteers, trip members, workers and partners. I just sent the tables and fields that were related to the specific issue. I send out receipts that most families want addressed to both the husband and wife. Therefore I need a field for the names along with a greeting field that includes both first names. I also send out letters and newsletters that I need that for. Spouses with different last names or hyphenated names are treated as individuals concerning receipts and I have a check box to select whether both or just one received newsletters. Children need to have their own records since they take part in trips individually and often without their parents. Of course, I also want to track which family they belong to, since often there are several from different families in the same town with the same last name. Step kids and kids are treated the same. If they live in the home they are considered kids.

I don't know X basic but can usually figure out the expressions since Lotus had all the programing written into expressions. Although Alpha does things somewhat different in some areas, I used the Alpha Sports example as a pattern for how I did much of mine. I have about everything else working with just a few issues like this left. I believe I need to have individual records for each person, so I can track their participation in trips any personal information that relates to their participation. I hope that gives a better idea of what I need. I understand my first post was pretty vague. Thanks for looking at it.

Roger

[Mike Wilson]

Re: Combining Husband and Wife names for newsletter name

Roger,
Having developed a couple of application for the same purpose you have described, I can share with you a method that I found to be the most robust for the purpose of what you're doing.

The premise is all persons are part of a family, and have 3 components to their personal identification number (pqid), a family number, their gender, and what position within the family they hold. The pqid is a combination of the 6 character family number, the gender of the person (1=male, 2=female), and the 2 character family position number (likely 2 not needed now days but maybe).

Family ID: 000001
Husband(father) - 000001.101,
wife (mother) - 000001.202
first child - 000001.103 if boy or 000001.203 if girl
second child - 000001.104 if boy or 000001.204 if girl

What this allows is for the capabilities of generating robust groupings, countings and relations. And it is all within a single table, not complex sets. You can do wonderful groupings and derive fun facts like "how many girls have an older brother?" and "how many families have 2 girls?" and "what is the gender of the youngest child?" ....based exclusively on the pqids.

It has worked very well using this strategy.

later:
Oh yes... the number for not being a mother or father and being single is family_no.100 man and .200 woman.

[Raymond Lyons]

Re: Combining Husband and Wife names for newsletter name

Much as I like Mike's scheme, I have a hunch Roger won't want to go in that direction without a working sample showing at the very least what he asked about for an addressee line for newsletters, etc. Still, I do like the one table idea.

Roger, I looked again at your set scheme in your sample DB and I still can't understand why you want to go with a set like that, and I don't yet see how using that set you could get the calculated field you asked for. I'm also not clear why the addressee line necessarily has to be a calculated field, though often that is the best way.

In any case, take a look sample DB I am attaching here. It has 2 simple tables and 1 set using those 2 tables. If nothing else it may give others lesson in one way (not necessarily the best way) to approach what I think you are trying to do. It also shows how to deal with many of the somewhat unorthodox family situations we have in today's world (mine being one of them).

Raymond Lyons

Later: Also note that if there were a need to have even the head of the household in the table with the rest of the family, just add them as "Head" under relation and have only common things in the Primary table of the set along with the family ID, i.e., the address, main telephone number, etc. Of course then you would have to modify the calculated fields in my example, but that should be easy.

[dayspring]

Re: Combining Husband and Wife names for newsletter name

Mike and Raymond,

Thanks for your suggestions. Raymond, It looks like you have what I am needing. I will need to study on it a bit and figure out how to get my info into that type of set up. It does make more sense that what I had. Thanks!

Roger

[dayspring]

Re: Combining Husband and Wife names for newsletter name

Raymond,

If I were to have the head of the household in the table with the rest of the family, do I still need to list him in the Primary Table under Hd_L_name and Hd_1stname? If not, I am not clear on how to write that expression for the calculated field.

Roger

[Raymond Lyons]

Re: Combining Husband and Wife names for newsletter name

Raymond,

If I were to have the head of the household in the table with the rest of the family, do I still need to list him in the Primary Table under Hd_L_name and Hd_1stname? If not, I am not clear on how to write that expression for the calculated field.

Roger

Roger,

Yes, I'm afraid you would need the head in the primary table, or at least I see no other way to do it at the moment.
Moreover, I overlooked another potential problem with the calcs I presented earlier. It is that the calcs don't work unless the child record you are on is the one for the spouse, as that is the only child record the calcs can "see". So for letters you'd have to make sure the spouse was the first child record.

So, I suggest that you make the Addressee be a single field in the primary table along with common family things like the mailing address, main phone #, etc. That way the calculated field for addressee is gone. If for some reason you need the Head names included in the child table, do so and I think I would then remove those fields from the primary table, as they would then seem to be redundant and unnecessary.

Raymond Lyons

[Mike Wilson]

Re: Combining Husband and Wife names for newsletter name

OK, Here is the example using my described method. The salutation is a calculated field in the Mailing Report. Hope this helps.

[Raymond Lyons]

Re: Combining Husband and Wife names for newsletter name

OK, Here is the example using my described method. The salutation is a calculated field in the Mailing Report. Hope this helps.

Like I said in the beginning, I like Mike's one table idea, at least from some standpoints. And yes, it meets the letter Roger's stated need. But Mike (and Roger), how are you going to deal spouses with different last names? Maybe you still do, but we as a society haven't lived in a world in which a husband and wife MUST have the same last name in many decades. So Mike, how could your scheme be made to work for people like me where my wife's last name is the last name she was born with and our kids last names are different yet because they took the last name of their biological father, my wife's former husband? Are we not a family in your world? My wife, our kids and our grandchildren would strongly say we are as much a family as any on the planet. End of a possibly unwarranted rant.

Mike, I am pretty sure you can deal with us in your scheme, though it may need to be modified in some way.

Raymond Lyons

[Raymond Lyons]

Re: Combining Husband and Wife names for newsletter name

.... End of a possibly unwarranted rant.

Mike, I am pretty sure you can deal with us in your scheme, though it may need to be modified in some way.

Raymond Lyons

Mike, I noticed that your table does have spouses with different last names but your cf_sal calc does not deal with them. I think they and the issue in my rant could be dealt with by modifying your calculated field in the report as follows:

cf_sal = IF(alltrim(lookup("folks","pqid="+quote(fmly_no+".202"),"Lname"))="",Alltrim(Fname)+" "+alltrim(Lname),IF(Lname<>alltrim(lookup("folks","pqid="+quote(fmly_no+".202"),"Lname")),if(right(pqid,1)="0",alltrim(Fname),alltrim(fname)+" "+alltrim(Lname)+" and "+alltrim(lookup("folks","pqid="+quote(fmly_no+".202"),"fname")))+" "+alltrim(lookup("folks","pqid="+quote(fmly_no+".202"),"Lname")),if(right(pqid,1)="0",alltrim(Fname),alltrim(fname)+" and "+alltrim(lookup("folks","pqid="+quote(fmly_no+".202"),"fname")))+" "+alltrim(Lname)))

On the other hand your way of using lookups could probably be used in the set I gave Roger and then the "Head" names could be dropped from the set's primary table and have the Head in the child table. On still another hand, I still see no good reason not to just have the Addressee field be one of the things in primary table (along with the address and so on), as kind of header data common to all members of the family. That way no complex calc field would be needed and one could even do things like "Dear Jack and Diane" in a letter if being so informal for some couples were appropriate. Yes, I know, Addressee and salutation are different things, so you might need 2 fields in the header if you wanted informality in a salutation.

But don't get me wrong, I am impressed with what you have done in a single table even though the scheme is a bit complex. Good work.

Raymond Lyons

[Mike Wilson]

Re: Combining Husband and Wife names for newsletter name

You got it, Raymond. Exactly.

[Raymond Lyons]

Re: Combining Husband and Wife names for newsletter name

You got it, Raymond. Exactly.

Mike (and Roger),

I have been playing around with the idea of using a lookup in the calc in my one to many set scheme but I can't make that work. Roger, let us know how you proceed.

Raymond Lyons

[Peter.Greulich]

Re: Combining Husband and Wife names for newsletter name

The premise is all persons are part of a family, and have 3 components to their personal identification number (pqid), a family number, their gender, and what position within the family they hold. The pqid is a combination of the 6 character family number, the gender of the person (1=male, 2=female), and the 2 character family position number (likely 2 not needed now days but maybe).

Family ID: 000001
Husband(father) - 000001.101,
wife (mother) - 000001.202
first child - 000001.103 if boy or 000001.203 if girl
second child - 000001.104 if boy or 000001.204 if girl

... the number for not being a mother or father and being single is family_no.100 man and .200 woman.

Mike,

What happens when Junior (000001.103) has his own family and becomes dad?

[Raymond Lyons]

Re: Combining Husband and Wife names for newsletter name

Mike,

What happens when Junior (000001.103) has his own family and becomes dad?

And they have to move back in with Grandma & Grandpa! Sadly, it happens! In fact it happened to a friend of mine just last month.

Ray