How do I "household" files?

[prbjorklund]

I'm very new to Filemaker and databases in general. I need some help! I am trying to figure out how to "household" a large list of voter files (30,000+). Specifically, I want to combine individuals who live at the same address (and have the same last name) so that I can mail only one letter to each household. I don't care about maintaining first names (I will mail to "The Smith Family"). I'm a graphic designer who uses InDesign, Illustrator, Photoshop, etc. all the time , but have never done anything with database programs. Any help that anyone can offer is greatly appreciated. I've been trying to figure this stuff out from "Filemaker Pro 7 Bible" but find the terminology and jargon somewhat daunting.

[QuinTech]

Hi, pjbjorklund, and welcome to the Forums!

The first thing I will say is that you should make sure the data you are getting is regularized to a very high degree. That is, the vast majority of them should use the same spellings, the same abbreviations, the same punctuation, the same word for unit (Apt, Unit, Room, Suite?), the same .....

At the risk of being overly pessimistic, if you don't have this, you should either give up or resign yourself to having a lot of bad matches. Having been through this before, I can tell you it's no picnic. If you have a small number of records, you can do it; but if you have 30,000 addresses to search, replacing every instance of "Str" with "St", "Street" with "St", etc., you may have a new career on your hands.

(I will assume you mean one file with 30,000 records, and not 30,000 files with several thousand records a piece.)

J

[prbjorklund]

Thanks for responding QuinTech. The records are in very good shape (I had nothing to do with creating them). They are very highly "regularized." It is one file with 30,000 records in it. Each record has 60 fields. The last name is in one field and the street address (e.g. 116 Mulberry Lane) is in another field. Town, zip, etc. are in other fields.

[QuinTech]

Beautiful. It would be even better if the address were parsed -- five fields (number, prefix, streetname, suffix, unit) instead of one -- but this will work.

I will assume that we are dealing with either (1) all 5-digit Zip codes or (2) all 9-digit Zip codes here. If not, create a calculation that returns only the first five, and use that as your Zip.

---

1. Create a relationship. You will need two table occurrences, each one based on the main voter table. The matches will be from Address to Address and Zip to Zip. Sort this relationship by Last Name, First Name, Middle Name, Suffix.

2. Create a calc field called "mailingAddress":

Case (

Count ( TO2::Address ) = 1 ,

TO2::Address ,

TO::Address = TO2::Address ,

TO2::Address

)

This field will be populated with the address under one of two conditions: (1) There is only one record for this address/Zip code combination; (2) there is more than one record, but this one is the first alphabetically.

Do a search for "*" in this field to find all records that meet this conditions, and you will have your mailing list. You can use a similar calc to the one above to make a name label:

Case (

Count ( TO2::Address ) = 1 ,

TO::First Name & " " & TO::Last Name ,

TO::Address = TO2::Address ,

"The " & TO::Last Name & " Family"

)

You might also try experimenting with refining the relationship and seeing what gives you the best results. You will have a few scenarios for matching addresses:

1. One household, everyone has the same name

2. One household, multiple names

3. Multiple households, multiple names

4. Multiple households, one name (this is highly likely to be a special case of #1, but a family member has rented a room -- it's close enough to number 1 that I would ignore it)

Scenario 1 is pretty straightforward, it is one household. Scenario 3 is also straightforward: Multiple households. Scenario 2 is more problematic: Is it two (or more) apartments where the resident failed to list his apartment number? Or is it a married couple where the woman kept her maiden name? Or is it domestic partners?

My preference would be to consider them separate households. The odds that it is an apartment building are probably greater than the same household/different name possibility. In any case, it is probably better to send too many to the same HH than to miss someone. For that reason, I would probably match the TO's on Address, Zip, AND Last Name.

J

[prbjorklund]

Thank you very, very much QuinTech. I'll give this a try. At the very least you've pointed me in the right direction to figure this out. Wading through "Filemaker Pro 7 Bible" is pretty tough when all the terms are so unfamiliar.