I want to calculate the percentage of a certain value across multiple fields in a record...

[eric.heggie]

I have a voter file that has unique fields for the past 10 or so primary elections. I am trying to determine the percentage of voting democrat or republican over that time.

What I am thinking is I have to some how have a calculation that adds 1 for every primary vote in a certain primary, then takes the total votes in both primaries. Take each unique primary vote and divide by the total.

[comment]

I am not sure I understand fully your description, but if you are using fields to record individual votes, you will have a hard time getting statistical summaries out of your data.

You need to split the vote data off to a second table, so that you have a Voters table (one record for each Voter, with a unique VoterID) and a Votes table (one record for each vote, indicating VoterID, which elections and what was the vote). A third table of Elections would be convenient, but not strictly necessary.

[eric.heggie]

Well the idea I have now for it is compute the number of times a person has voted in multiple democrat and republican primaries. SO have something that search's over 10 fields that either have DEMO or REPB as the value in each field, or no value at all. For every DEMO or REPB it finds in each primary field, of which there are ten. It adds 1 to a DEMO total primary votes field or calculated result, and a REPB primary votes field or calculated result. Then it takes these two numbers once it is finished. Adds them together, and then divides each by the sum of each. Thus giving a percentage.

[comment]

Well, I suppose you could do that. You could also take a piece of paper, go over the records one by one and jot down how many of each are there, then sum up the numbers. These two methods are roughly similar in efficiency and elegance.

Anyway, if you insist, you can do the finger count by:

PatternCount ( Field1 & Field2 & Field3 & ... Field10 ; "DEMO" )

[eric.heggie]

Yeah I don't know if I was clear on what I want. I want a count of how many times each voter has voted in a dem primary and a Repub primary. Then I want to total those numbers up and find the percentage for each.

[comment]

Yes, you were quite clear. The calculation above counts "how many times each voter has voted in a dem primary" (I think). You need another one just like that, searching for "REPB" instead of "DEMO". Then two summary fields to total the results over the entire found set. Then divide one of them by the sum of the two to get a percentage.

I still think you should split the data off to individual records.

[eric.heggie]

Im a newbie and I didn't understand earlier why you think I should do that, so I am going to ask why?

[comment]

Because your method does not allow you to answer the following question (at least not without going through major hoops) How many voted REPB in 1996?

That's only an example, of course - there are a lot more stats you can get very easily when your votes are individual records. Even your current problem would be a lot easier to handle: you would simply sub-summarize the records by vote, and use ONE summary field, defined as percentage of total.

[eric.heggie]

to do that I would set up a second table with just the primary voting history fields?

[comment]

Yes and no. That is yes, a second table. No regarding the fields. Let's say your current table has these fields:

• VoterID (unique)

• Vote1 (refers to 1992)

• Vote2 (refers to 1996)

• Vote3 (refers to 2000)

• Vote4 (refers to 2004)

Your new table would have these fields:

• VoterID

• Year

• Vote

To populate this table with data, you would import the records from the current table 4 times, each time importing from the same VoterID field and a different VoteN field (the Year field has to be set, or pre-set, separately for each import).