Managing Voter Files

[jess]

I have voter registration file with individual records for each voter. My problem is that I want to combine records with the same address into a single household. But before I can do that I will need to analyze the voters in the same houshold and categorize them. For example"

Jane Smith 123 Rocky Road Democrat

Mike Smith 123 Rocky Road Unaffiliated

another category would be:

Bob Jones 111 Fire Road Republican

Tom Jones 111 Fire Road Unknown

And so on...I have about 5 different scenarios that I want to categorize.

The eventual output needs to be one record for every address with a category code that defines the voter combination in the household.

I'm not really sure where to start. Any help is greatly appreciated!

JCM

[jess]

I forgot to mention that all the data is in really good shape. Individual fields for FIRST and LAST names....and the ADDRESS field is consistent too.

Also..during the process where I combine the records into one, could I also set a field name to be a household name? e.g. "The Smiths"..

[Dane]

Assuming you only have households of one or two voters, this would be a good situation for the classic use of powers-of-two coding for your category codes.

E.g.:

Democrat = 1

Republican = 2

Unaffiliated =4

Libertarian = 8

Monarchist =16

Anarchist = 32

First perform a check to see if both addressees have the same party affiliation; if so, then category code = whatever that party is.

If they're different, add the two numbers together. The resulting number is unique for each possible pair combination; e.g., Republican + Democrat = 3; Democrat + Libertarian = 9, Anarchist + Republican = 34 (and what a marriage that must be...), etc.

Changing addressee to "The Smiths" would require a check for more than one person of that last name at that address; if only one person, address goes to

FIRSTNAME & " " & LASTNAME

if more than one, address goes to

"The " & LASTNAME & "s"

(You might want to get anal retentive about grammar and check for last letter of the last name being an "s"....)

Good luck dealing with households like mine, where my wife & I have different last names....

-Dane

[comment]

And if there are three people in the same household?

To me, the 'classic' coding would be to take n as the number of possible party affiliations (with a generous spare, in case more are added later), then code the first person as x_1, the second person as x_2 * n, the third person as x_3 * n^2, etc.

With say 10 party slots, code 205 would signify "3 people, one affiliated with Party#5, one with Party#0, and the third with Party#2".

A more important point (I think) is that there should be no combining of records. Instead, a related household record should be created in a separate table for each combination. This might make the whole coding issue moot, since the original affiliation data remains accessible.

[jess]

Comment,

I apologize for my newbie-ness here. But your last paragraph seemed to be the answer that best fits. If I create another table with a household ID how do I create the unique IDs and associate them with the voters?

Sorry for the lack of clarity, my mind is getting mushy from all of this...

[comment]

Two questions:

1. What are your criteria for grouping the records? Is it "the same last name at the same address"?

2. Is this a one-off task, or do you have to do this periodically?

[jess]

The only criteria for combining the records will be the address (including City). However I also have to categorize the new household record by the combination of voters with the same address.

For example the following records like this:

John Smith 1111 Rocky Road Jonestown REP

Lisa Smith 1111 Rocky Road Jonestown DEM

Tony Neils 1111 Rocky Road Jonestown GRN

The above set needs to be combined into something like this:

1111 Rocky Road Jonestown CategoryA

I have 5 different categories based on different combinations. I don't care how may people were in the household or the quantity of any particular affiliation.

This would only occur once, not periodicaly. I am just trying to fix this data so it can be imported into some mapping software.

Edited May 5, 2006 by Guest

[comment]

Hmmm... are there no two households with the same address, e.g. a condominium?

Anyway, you haven't answered my second question.

EDIT: oh, now you have. Hold on for a while...

Edited May 5, 2006 by Guest

[jess]

If there was an apartment complex it would still be a unique address. For example

"1111 Rock Road #1"

"1111 Rock Road #2"

[comment]

OK, let's call your existing table Voters.

1. First, make sure you have some field in Voters that is unique to each record. If you don't, define a number field VoterID, set it to auto-enter a serial number, show all records, click into the VoterID field and choose Records > Replace Field Contents > Replace with serial numbers (from 1, update...).

2. Go to DefineDatabase > Relationships and click the + button to add a new table occurence of the Voters table. Name it Similar.

3. Create a relationship from Voters to Similar:

Voters::Address = Similar::Address

AND

Voters::City = Similar::City

4. Define a new calculation field cUnique (result is number) =

VoterID = Similar::VoterID

I shall pause here, and ask you what your 5 combinations are.

Another question: do you want your results in a new Filemaker table, or do you intend to export it for use in another application?

[jess]

Wheh! I think you are on fire!

Category

A = REP

B = REP or any other value

C = any value but no REP or DEM

D = DEM

E = DEM and any other value except REP

other possible values are as follows:

GRN

AM

UNK

NAF

I think it would be best to put in another table.

[comment]

Do I understand this correctly:

A = all the voters in this household are REP

B = some but not all voters in this household are REP

C = no voters in this household are REP, AND no voters in this household are DEM

D = all the voters in this household are DEM

E = some but not all voters in this household are DEM, AND no voters in this household are REP

[jess]

that is correct!

[comment]

OK, then let's move on:

5. Create a new table called Households, with (at least) the following fields:

HouseholdsID - number, auto-enter serial number

Address - Text

City - Text

6. Define a relationship from Voters to Households:

Voters::Address = Households::Address

AND

Voters::City = Households::City

7. Go to the Voters layout, go to Find mode, enter "1" (without the quotes) into the cUnique field and click Find.

8. Go to the Households layout, choose File > Import Records > File.., select your file and import from Voters to Households (match Address and City fields, enable auto-enter).

Now you should have a record for each unique household, and can move to calculating the combinations. I trust you're keeping up with this?

[jess]

so far I'm with you all the way....

[comment]

Good.

9. Define a value list named "Affiliations" using values from field Affiliation (or whatever your party affiliation field is named) in Voters, including only related values starting from Households.

10. In the Household table, define an unstored calculation field cCategory (result is Text) =

Let (

list = ValueListItems ( Get (FileName) ; "Affiliations" )

;

Case (

list = "REP" ; "A" ;

not IsEmpty ( FilterValues ( "REP" ; list ) ) ; "B" ;

IsEmpty ( FilterValues ( "DEM" ; list ) ) ; "C" ;

list = "DEM" ; "D" ;

not IsEmpty ( FilterValues ( "DEM" ; list ) ) ; "E" ;

"ERROR"

)

)

That should be about it.

[jess]

Wait...

Back in step 8 it sounds like I should have only one HouseholdID for every address. Unfortunately, I am scrolling through records and seeing different HouseholdIDs for the same address.

[comment]

Hmmm.. Back in step 7, when you found unique only, did you not get only one record per address?

[jess]

It appears not. See attached.

Voters.zip

Edited May 5, 2006 by Guest

[comment]

Sloppy work in step 6 - your relationship is NOT as specified.

Also, make sure your calc field (step 10) is set to unstored.

[comment]

I have to leave now. Here's my file with your data (minus the headers record). Hope you can sort it out.

VotersC.fp7.zip

[jess]

Hmm...I really can't see what I'm doing wrong. I click the add relationship button...then match up the Address to Address field and City to City fields.

What am I not seeing here?

[jess]

Wow....truly impressed. You MAC OS guys really know your stuff. I can't thank you enough!

However, I'm gonna drop the bomb on you. I had hoped to export the unique address and category code from the newly created table. Unfortunately it spits out "0" for every category code and I can't figure out how to prevent this.

Sorry for jerking you around this, you were pretty specific about this question early on in the thread. I guess I just assumed it could be exported when in a new table.

Edited May 5, 2006 by Guest

[comment]

Yes, it can be exported. I only asked because if ALL you wanted to do is export, we could have saved a few steps. But now YOU need to get specific, and describe in detail what you did.

(BTW, I'm not a "MAC OS guy" (whatever that means), and I have met quite a few that don't know jack.)

[jess]

Hmm...well all I did was open your file and then go to File -> Export Records -> chose a filename.xls then from the Households layout chose the following sort order:

HouseholdID

Address

City

cCategory

All I get is "0" in the cCategory field.

[comment]

Hmmm... Strange. I get the same the result here - but if I export in tab-separated or merge format, it works just fine. Seems like FMP's Excel export has a bug (at least in my 8.0v1 - have you applied any of the updates?).

Anyway, you should be able to open either of the two other formats in Excel.

Alternatively, try this:

Go to Define Database > Fields, and duplicate the cCategory field. Click OK to leave.

Go to Define Database > Fields again, and change the duplicated field to Text.

Export using the duplicated field instead of cCategory.

[comment]

WAIT, WAIT!

OK, this time it's MY sloppiness. Just change the calculation result of cCategory to type Text, and that should be it. I SAID it should be Text in step 10, but...

[jess]

Ahh...

Thanks again for all your help. You've been a real life saver for me (and I'm sure a lot of other people in the future).

Edited May 5, 2006 by Guest

[jess]

Comment...I think there is one category I left out. REP and DEMS living together in the same household.

How would I modify your calculation:

Let (

list = ValueListItems ( Get (FileName) ; "Affiliations" )

;

Case (

list = "REP" ; "A" ;

not IsEmpty ( FilterValues ( "REP" ; list ) ) ; "B" ;

IsEmpty ( FilterValues ( "DEM" ; list ) ) ; "C" ;

list = "DEM" ; "D" ;

not IsEmpty ( FilterValues ( "DEM" ; list ) ) ; "E" ;

"Unexpected"

)

)

/*

A = all REP

B = some REP

C = no REP AND no DEM

D = all DEM

E = some DEM AND no REP

*/

[comment]

Why not simply wait - surely they won't stay together for long...

Before I get into this, you need to clarify:

It seems you view the Affiliations as 3 categories: REP, DEM and anything else. But this produces SEVEN possible combinations:

REP DEM Other

REP DEM

REP Other

DEM Other

REP

DEM

Other

Currently, your classification is:

REP DEM Other = B

REP DEM = B

REP Other = B

DEM Other = E

REP = A

DEM = D

Other = C

So category B actually contains 3 distinct sub-categories. Do you want to split off the category of households with REP and DEM and possibly other? Or is it REP and DEM and nothing else? Please amend the above table accordingly.